Color Cells based on values. Ex: if D3 = Yes then A24 is filled gray and if No then no fill. See attached spreadsheet and description

darthvader747
darthvader747 used Ask the Experts™
on
Color certain cells gray-25% based on values in other cells. Specifics below:

If D3 = Yes Then A24 is Filled with Gray-25%
Else If D3 is 'No' then No Fill in A24

If D4 = Yes Then A25 is Filled with Gray-25%
Else If D4 is 'No' then No Fill in A25

If D5 = Yes Then A26 is Filled with Gray-25%
Else If D5 is 'No' then No Fill in A26

If D6 = Yes Then A27 is Filled with Gray-25%
Else If D6 is 'No' then No Fill in A27

Same logic till D13 and A 34
FillColor.xls
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Do a help search on Conditional Formatting.
it may depend on which version of Excel you are working with.

Author

Commented:
I have excel 2003 and I would like this done in the interest of time.
Select Cells A24-A34
From top toolbar, select Format, Conditional Formatting
condition1:
change from "cell value is" to "Formula is"
in the formula bar type:
=IF(D3="Yes","true","false")
click the Format button, select the patterns tab, and select the shading you want.
click OK.

OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

to make it a bit more generic, in case someone types YES, or yes, you could change the formula to:
IF(upper(D3)="YES","True","False")
actually you don't need the upper, it is normally case insensitive, if you want exact you can use the exact function

Author

Commented:
=IF(D3="Yes","true","false")

In this formula does excel evaluate 'True' and 'False' as logical statements?

Could I replace True with say 'T' and false with 'F'? I am trying to understand if true and false are like keywords.

Your solution works!
I haven't tried replacing with T or F, it may work, also 1 and 0 may work as well, as excel usually evalutes 1=true 0=false.  I would leave it as full true and false just for ease of understanding when you go back to it.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial