Conditional format question.

[dbmathis]

Hi all,

How would I go about creating a conditional format that checks for the following:

Format is the following two are true:

If D10 = "Test"

If self (cell of conditional format) is = ""

Thanks.

 

[just_jon]

=IF(AND(D10="Test",LEN(A1)=0)

Or

=IF(AND(D10="Test",A1="")

Assumes A1 is the cell of interest.

 

[dbmathis]

The issue I am having is that I need to format 2000 cells at once like this? Is there a painless way to so this?

 

[just_jon]

Does the D10 reference change as the A1 reference changes?

Are the cells contiguous?

 

[dbmathis]

Nope, just A

 

[HalfAce]

Hello dbmathis,
If you wanted to test all 2000 cells against D10 (for example) and say your first (of the 2000 cells) was A1, you could CF A1 with the formula:
=(AND($D$10="Test")*(A1=""))
Then use the format painter to paint the other 1999 cells.

If this isn't exactly what you're looking to do, then some other combination of absolute and/or relative references in the formula is going to be what you need.

 

[just_jon]

Then in the upper-most, left-most cell in your range -- we'll assume it's A1 here -- enter:

=IF(AND($D$10="Test",A1="")

in your data validation, then

click on cell A1 and click copy

highlight the rest of the range and click Edit | Paste special, select Validation.