Conditional format question.

dbmathis

Well-known Member
Joined
Sep 22, 2002
Messages
1,064
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.
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

just_jon

Legend
Joined
Sep 3, 2002
Messages
10,473
=IF(AND(D10="Test",LEN(A1)=0)

Or

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

Assumes A1 is the cell of interest.
 

dbmathis

Well-known Member
Joined
Sep 22, 2002
Messages
1,064
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

Legend
Joined
Sep 3, 2002
Messages
10,473
Does the D10 reference change as the A1 reference changes?

Are the cells contiguous?
 

dbmathis

Well-known Member
Joined
Sep 22, 2002
Messages
1,064

ADVERTISEMENT

Nope, just A
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453
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

Legend
Joined
Sep 3, 2002
Messages
10,473
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,673
Messages
5,573,594
Members
412,537
Latest member
Mohamed_5966
Top