Change Cell color based on other cell content

**Mies07** · 07-05-2012, 03:24 PM

Is there a way to change a cell color based on wether or not other cells have content?
Something like
(if any cell in cell range A1-X200 is not empty, then cell X201 is green fill)
(else X201 is no fill)
Thanks!

**NBVC** · 07-05-2012, 03:57 PM

Select cell X201 and go to Home|Conditional Formatting|New Rule

Select "Use a formula to determine which cells to format"

enter formula:

=COUNTIF($A$1:$X$200,"<>")

click format and choose from Fill tab.

**Mies07** · 07-10-2012, 06:18 PM

Thanks, that worked.
Now.. how do I drag that down so each cell under that changes format fill according to the cells in row to immediate left? (Like you can do with a sum formula, it will automatically increment range row numbers as you drag it down to cells below) - unfortunately this does not seem to work with conditional formatting, or is there a secret key to hold down to drag conditional formatting?
Thanks

**NBVC** · 07-11-2012, 07:50 AM

I am not sure exactly what you mean.

You want to use the same format in X202 and highlight it if A2:X201 are all blank? doesn't seem to make sense..

**Mies07** · 07-11-2012, 09:20 AM

Found answer - difference between absolute and relative references
Put the $ sign on each part of the cell that you don't want to move, i.e. $A$39 - cell A39 will not be copied relatively, it will always be A39 as you copy the formula down. Remove the $ signs and it will be copied relatively (references increment.)
So:
=COUNTIF($A$1:$X$200,"<>") (absolute, not incrementally dragable)
or
=COUNTIF(A1:X200,"<>") (relative, incrementally dragable)