Overclock.net › Forums › Software, Programming and Coding › Other Software › Excel help please, quickly would be nice
New Posts  All Forums:Forum Nav:

Excel help please, quickly would be nice

post #1 of 18
Thread Starter 
Guys,

I'm trying to get cell D4 to check the cell C4 for a specific text (Yes) and then colour the Cell in D4 green.

How... The Hell... Do I do this.

I tried =cell text and =Istext and I can't get it to work.

Help frown.gif

Cheers

Comps
post #2 of 18
Quote:
Originally Posted by compuman145 View Post

Guys,

I'm trying to get cell D4 to check the cell C4 for a specific text (Yes) and then colour the Cell in D4 green.

How... The Hell... Do I do this.

I tried =cell text and =Istext and I can't get it to work.

Help frown.gif

Cheers

Comps

If you're doing through Excel and not VBA, use conditional formatting to change the color of cells.

Home -> Conditional Formatting -> New Rule -> Use a formula to determine which cells to format
Once again...
(13 items)
 
  
CPUMotherboardGraphicsRAM
i7 920 [4.28GHz, HT] Asus P6T + Broadcom NetXtreme II VisionTek HD5850 [900/1200] + Galaxy GT240 2x4GB G.Skill Ripjaw X [1632 MHz] 
Hard DriveOSMonitorKeyboard
Intel X25-M 160GB + 3xRAID0 500GB 7200.12 Window 7 Pro 64 Acer H243H + Samsung 226BW XARMOR-U9BL  
PowerCaseMouseMouse Pad
Antec Truepower New 750W Li Lian PC-V2100 [10x120mm fans] Logitech G9 X-Trac Pro 
  hide details  
Reply
Once again...
(13 items)
 
  
CPUMotherboardGraphicsRAM
i7 920 [4.28GHz, HT] Asus P6T + Broadcom NetXtreme II VisionTek HD5850 [900/1200] + Galaxy GT240 2x4GB G.Skill Ripjaw X [1632 MHz] 
Hard DriveOSMonitorKeyboard
Intel X25-M 160GB + 3xRAID0 500GB 7200.12 Window 7 Pro 64 Acer H243H + Samsung 226BW XARMOR-U9BL  
PowerCaseMouseMouse Pad
Antec Truepower New 750W Li Lian PC-V2100 [10x120mm fans] Logitech G9 X-Trac Pro 
  hide details  
Reply
post #3 of 18
Just use conditional formatting (Home Tab > Conditional Formatting). You can specify a condition (i.e. C4="Yes") and then adjust the formatting of the cell (including the fill colour).

Edit: ninja'd by DuckieHo. tongue.gif
KamIX
(22 items)
 
  
CPUMotherboardGraphicsRAM
Intel Core i7 7700K Asus Maximus IX Hero Asus Strix GTX1080 32GB G.Skill Ripjaws V DDR4-2666 CL15 
Hard DriveHard DriveHard DriveCooling
Plextor M8Pe 512GB (NVMe w/ Heatsink) Samsung 850 Evo 500GB Western Digital Black 4TB Noctua NH-D15 
OSMonitorKeyboardPower
Windows 10 64-bit Dell UltraSharp U2515H (1440p, 60Hz IPS) Vortex Pok3r (MX Blue, Double-Shot PBT Keycaps) EVGA SuperNOVA 750 G3 
CaseMouseMouse PadAudio
Coolermaster MasterCase 5 Pro w/ 4x Noctua NF-A14 Logitech G900 Zowie G-SR FiiO E10K 
AudioAudioAudioOther
Sennheiser HD598 SE (Black) Mackie CR3 Blue Microphones Snowball iCE (Black) Steam Controller 
OtherOther
CM Storm Skorpion Microsoft LifeCam Studio 
  hide details  
Reply
KamIX
(22 items)
 
  
CPUMotherboardGraphicsRAM
Intel Core i7 7700K Asus Maximus IX Hero Asus Strix GTX1080 32GB G.Skill Ripjaws V DDR4-2666 CL15 
Hard DriveHard DriveHard DriveCooling
Plextor M8Pe 512GB (NVMe w/ Heatsink) Samsung 850 Evo 500GB Western Digital Black 4TB Noctua NH-D15 
OSMonitorKeyboardPower
Windows 10 64-bit Dell UltraSharp U2515H (1440p, 60Hz IPS) Vortex Pok3r (MX Blue, Double-Shot PBT Keycaps) EVGA SuperNOVA 750 G3 
CaseMouseMouse PadAudio
Coolermaster MasterCase 5 Pro w/ 4x Noctua NF-A14 Logitech G900 Zowie G-SR FiiO E10K 
AudioAudioAudioOther
Sennheiser HD598 SE (Black) Mackie CR3 Blue Microphones Snowball iCE (Black) Steam Controller 
OtherOther
CM Storm Skorpion Microsoft LifeCam Studio 
  hide details  
Reply
post #4 of 18
Thread Starter 
I tried that and it didn't work frown.gif

I did conditional formatting, new rule doing the last option (use a formula) then entering C4=Yes and it doesn't format D4 as green.

Also I need this to move down through C column checking and then marking the relevant D column green.
post #5 of 18
Quote:
Originally Posted by compuman145 View Post

I tried that and it didn't work frown.gif

I did conditional formatting, new rule doing the last option (use a formula) then entering C4=Yes and it doesn't format D4 as green.

Also I need this to move down through C column checking and then marking the relevant D column green.

You got the equation formatted correctly? It should look like this.



From there, if you want to extend the fomatting down the column, just select the entire column (or the range that applies). The formula for the condition will be the same as shown above, but the "$" infront of the 4 needs to be removed (i.e. =$C4="Yes")
KamIX
(22 items)
 
  
CPUMotherboardGraphicsRAM
Intel Core i7 7700K Asus Maximus IX Hero Asus Strix GTX1080 32GB G.Skill Ripjaws V DDR4-2666 CL15 
Hard DriveHard DriveHard DriveCooling
Plextor M8Pe 512GB (NVMe w/ Heatsink) Samsung 850 Evo 500GB Western Digital Black 4TB Noctua NH-D15 
OSMonitorKeyboardPower
Windows 10 64-bit Dell UltraSharp U2515H (1440p, 60Hz IPS) Vortex Pok3r (MX Blue, Double-Shot PBT Keycaps) EVGA SuperNOVA 750 G3 
CaseMouseMouse PadAudio
Coolermaster MasterCase 5 Pro w/ 4x Noctua NF-A14 Logitech G900 Zowie G-SR FiiO E10K 
AudioAudioAudioOther
Sennheiser HD598 SE (Black) Mackie CR3 Blue Microphones Snowball iCE (Black) Steam Controller 
OtherOther
CM Storm Skorpion Microsoft LifeCam Studio 
  hide details  
Reply
KamIX
(22 items)
 
  
CPUMotherboardGraphicsRAM
Intel Core i7 7700K Asus Maximus IX Hero Asus Strix GTX1080 32GB G.Skill Ripjaws V DDR4-2666 CL15 
Hard DriveHard DriveHard DriveCooling
Plextor M8Pe 512GB (NVMe w/ Heatsink) Samsung 850 Evo 500GB Western Digital Black 4TB Noctua NH-D15 
OSMonitorKeyboardPower
Windows 10 64-bit Dell UltraSharp U2515H (1440p, 60Hz IPS) Vortex Pok3r (MX Blue, Double-Shot PBT Keycaps) EVGA SuperNOVA 750 G3 
CaseMouseMouse PadAudio
Coolermaster MasterCase 5 Pro w/ 4x Noctua NF-A14 Logitech G900 Zowie G-SR FiiO E10K 
AudioAudioAudioOther
Sennheiser HD598 SE (Black) Mackie CR3 Blue Microphones Snowball iCE (Black) Steam Controller 
OtherOther
CM Storm Skorpion Microsoft LifeCam Studio 
  hide details  
Reply
post #6 of 18
Formula you want is:
Code:
=IF($C5="Yes", 1, 0)



Note, you can also allow for case variances (Yes, yes YEs, yeS etc.) by adding in a "Upper" operator:
Code:
=IF(UPPER($C5)="YES", 1, 0)




Edit: Too many darn ninjas in this thread ninja.gif
Edit2: Missed a parenthesis doh.gif
Edited by GingerJohn - 10/29/13 at 8:31am
Main
(21 items)
 
HTPC
(10 items)
 
 
CPUMotherboardGraphicsRAM
i5 2550k P8P67 Pro Sapphire HD 7950 G.Skill RipJaws X 1600 Cas 9 
Hard DriveHard DriveHard DriveCooling
Corsair Force 120 WD Blue 500GB WD Caviar Green 1TB XSPC RayStorm 
CoolingCoolingCoolingCooling
RX240 MCR 220 EK 7950 Copper Acetal  DDC-1T 
OSMonitorMonitorKeyboard
Windows 7 64-bit Dell U2311H Oculus Rift DK2 Ducky Shine 3 MX Brown 
PowerCaseMouseAudio
Corsair TX 750W CoolerMaster CM690 II G500 Klipsch ProMedia 2.1 
Audio
Asus Xonar DX 
CPUMotherboardRAMHard Drive
A10-6800K Gigabyte GA-F2A85XN-WIFI G Skill 1600 CAS9 Kingston SSD Now 60GB 
Hard DriveOptical DriveCoolingOS
WD Caviar Blue 1TB LG Slim Blu-Ray player Silverstone NT06-PRO  Widows 7 Home Premium 
PowerCase
Silverstone Sfx Series ST45SF 450W Silverstone SG05 
  hide details  
Reply
Main
(21 items)
 
HTPC
(10 items)
 
 
CPUMotherboardGraphicsRAM
i5 2550k P8P67 Pro Sapphire HD 7950 G.Skill RipJaws X 1600 Cas 9 
Hard DriveHard DriveHard DriveCooling
Corsair Force 120 WD Blue 500GB WD Caviar Green 1TB XSPC RayStorm 
CoolingCoolingCoolingCooling
RX240 MCR 220 EK 7950 Copper Acetal  DDC-1T 
OSMonitorMonitorKeyboard
Windows 7 64-bit Dell U2311H Oculus Rift DK2 Ducky Shine 3 MX Brown 
PowerCaseMouseAudio
Corsair TX 750W CoolerMaster CM690 II G500 Klipsch ProMedia 2.1 
Audio
Asus Xonar DX 
CPUMotherboardRAMHard Drive
A10-6800K Gigabyte GA-F2A85XN-WIFI G Skill 1600 CAS9 Kingston SSD Now 60GB 
Hard DriveOptical DriveCoolingOS
WD Caviar Blue 1TB LG Slim Blu-Ray player Silverstone NT06-PRO  Widows 7 Home Premium 
PowerCase
Silverstone Sfx Series ST45SF 450W Silverstone SG05 
  hide details  
Reply
post #7 of 18
what formula are you using in D4. You could use If in d4 for c4 then use conditional formatting on the value you placed as true.. or are you not wanting a value at all in D

my example
Code:
=IF(C4="yes",1,0) 

then just setup conditional formatting to show green for all values above 0

that's the way ive always done it... my be something simpler out there.
Home
(17 items)
 
Server
(15 items)
 
 
CPUCPUMotherboardGraphics
intel Xeon 2670 intel xeon 2670 Intel S2600CP Evga 980ti 
RAMHard DriveHard DriveOptical Drive
64gb Ecc 24x 128gb ssds  Crucial Mx1000 Asus 
OSMonitorMonitorKeyboard
Windows 10 Pro Sony XBR43X830C 43" 4K Monoprice 28" 4k Max Keyboards NightHawk X8 Custom 
PowerCaseMouseMouse Pad
Evga Supernova 850w  Norco Rpc-4224 Logitech G900 Chaos Steelseries QCK 
Audio
FIIO E7/E9 combo 
CPUCPUMotherboardRAM
Intel L5640 Intel L5640  Supermicro - X8DT3 24Gb ECC 
Hard DriveHard DriveHard DriveOS
4x 128gb ssd's raid 0 - Cache Wd Red 3tb Seagate 8tb smr - parity Unriad 6.2 
PowerCaseOtherOther
ThermalTake 700 watt  Norco 4220 HP sas expander Lsi 9211-8i 
  hide details  
Reply
Home
(17 items)
 
Server
(15 items)
 
 
CPUCPUMotherboardGraphics
intel Xeon 2670 intel xeon 2670 Intel S2600CP Evga 980ti 
RAMHard DriveHard DriveOptical Drive
64gb Ecc 24x 128gb ssds  Crucial Mx1000 Asus 
OSMonitorMonitorKeyboard
Windows 10 Pro Sony XBR43X830C 43" 4K Monoprice 28" 4k Max Keyboards NightHawk X8 Custom 
PowerCaseMouseMouse Pad
Evga Supernova 850w  Norco Rpc-4224 Logitech G900 Chaos Steelseries QCK 
Audio
FIIO E7/E9 combo 
CPUCPUMotherboardRAM
Intel L5640 Intel L5640  Supermicro - X8DT3 24Gb ECC 
Hard DriveHard DriveHard DriveOS
4x 128gb ssd's raid 0 - Cache Wd Red 3tb Seagate 8tb smr - parity Unriad 6.2 
PowerCaseOtherOther
ThermalTake 700 watt  Norco 4220 HP sas expander Lsi 9211-8i 
  hide details  
Reply
post #8 of 18
Thread Starter 
OK cool

Now what I can do is if it's green, then fill it with "Complete" using more conditional formats biggrin.gif

Thanks very much all biggrin.gif

Comps
post #9 of 18
Also, you can also build a custom VBA function as well (but totally unnecessary in this case).
Once again...
(13 items)
 
  
CPUMotherboardGraphicsRAM
i7 920 [4.28GHz, HT] Asus P6T + Broadcom NetXtreme II VisionTek HD5850 [900/1200] + Galaxy GT240 2x4GB G.Skill Ripjaw X [1632 MHz] 
Hard DriveOSMonitorKeyboard
Intel X25-M 160GB + 3xRAID0 500GB 7200.12 Window 7 Pro 64 Acer H243H + Samsung 226BW XARMOR-U9BL  
PowerCaseMouseMouse Pad
Antec Truepower New 750W Li Lian PC-V2100 [10x120mm fans] Logitech G9 X-Trac Pro 
  hide details  
Reply
Once again...
(13 items)
 
  
CPUMotherboardGraphicsRAM
i7 920 [4.28GHz, HT] Asus P6T + Broadcom NetXtreme II VisionTek HD5850 [900/1200] + Galaxy GT240 2x4GB G.Skill Ripjaw X [1632 MHz] 
Hard DriveOSMonitorKeyboard
Intel X25-M 160GB + 3xRAID0 500GB 7200.12 Window 7 Pro 64 Acer H243H + Samsung 226BW XARMOR-U9BL  
PowerCaseMouseMouse Pad
Antec Truepower New 750W Li Lian PC-V2100 [10x120mm fans] Logitech G9 X-Trac Pro 
  hide details  
Reply
post #10 of 18
Thread Starter 
Yeah,

What I've got to do is a server task list and I want it to show up green and red for if the task has been completed or not.

Any idea how I use this conditional formatting to check a colour and then fill it with text saying either "PASS" or "FAIL" as well as keeping the colour?

I know, I suck at this stuff..
New Posts  All Forums:Forum Nav:
  Return Home
  Back to Forum: Other Software
Overclock.net › Forums › Software, Programming and Coding › Other Software › Excel help please, quickly would be nice