Conditional Formatting Question

  • Thread starter Thread starter libby
  • Start date Start date
L

libby

I have two columns of data in A in B such as:

A B
Data1
Data2 FALSE
Data3
Data4
Data5

In column B there is either a TRUE or FALSE or a blank. There may be more
than 1 instance of TRUE/FALSE in column B. However, if 1 is FALSE, all
instances are FALSE and if 1 is TRUE, all instances are TRUE.

I would like to format Data1 through Data5 in column A to be grey if any
cell in B1:B5 = FALSE. I would also like to do this with relative cell
references in order to use the format painter to repeat the formula for other
data sets.

I have tried:

Formula = AND(COUNTIF(B1:B5,FALSE)>0)

This works if the cell references are absolute, but if I change to relative
references, only cells A1:A2 for the example above are formatted grey.

Any suggestions?
 
I would also like to do this with relative cell references

You can't use relative references because you're applying the format to a
range of cells. The reference in the COUNTIF would have to use absolute
rows:

=COUNTIF(B$1:B$5,FALSE)
 
Back
Top