Conditional formatting based on two sets of criteria

[timwilliams77]

Hi

i would like to format column H of my worksheet with a RED colour if the cell is blanc, however I also only wish to format it if column F has a value of 1 in it

how can this be done?

tim

 

[xlbo]

Use the "Formula Is" option rather than the "Value Is" option and enter something along the lines of

=AND($H2="",$F2=1)

for your criteria

This would be for where your data starts in row 2...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[timwilliams77]

Hi Geoff

Thanks for your response, however where is the formula is option?, and how do I enter it for all rows?

 

[SkipVought]

Cell Value Is: is a DROPDOWN.

Select ALL cells you want CFed.

Apply the Criteria, based on the top-left cell. This will propogate to all selected cells.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[timwilliams77]

sorry still struggling

I am highlighting all of column H, then going to the conditional formatting option and then choose new rule and then use a formula to determine which cells to format and then i enter the below, this does nothing

=formulais(AND($H$2="",$F$2=1,$O$2="Open"))

 

[SkipVought]

[tt]
DropDown Formula Text Box
Formula is: =AND($H2="",$F2=1,$O2="Open")
[/tt]
I'm guessing that you want to test the values in columns H, F & O on EACH ROW, so notice that I changed the ABSOLUTE reference for the row to RELATIVE.

If you don't know what that means, check out Excel HELP on About cell and range references & Switch between relative, absolute, and mixed references.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[timwilliams77]

sorry to be a pain, I have applied what you said (i think i have done it correctly)

however the result is not correct, I have filtred on cells with a red colour and the ones that are red are cells without a 1 in F and one with a value in H

Tim

 

[SkipVought]

Please open CF and copy the formula. ALSO post what CELL you have selected.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[timwilliams77]

=AND($H2="",$F2=1,$O2="Open")

tried it just in cell 2, but it only Applied the formatting to cell 2

so i highighted the whole row and entered the value and thats when i giot the results above

Tim

 

[xlbo]

If you select the whole column and then use H2 as your basis it won;t work - select from H2 down to the bottom of your dataset and use the same formula...can't see it being an issue!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question