SOLVED

# Conditional Formatting

Copper Contributor

# Conditional Formatting

Please help! I got this to work about 10 years ago but with copying/pasting to a new worksheet each year the format disappeared somewhere and I'm lost!

Columns as follows:

Name, Opening Balance, Sales, closing balance

Apple,     10                    , 10    , 0  (Zero, nil)

I want to apply a conditional format to column 'Name' so that if 'Closing Balance' = 0, the name (Apple) will show in red ([Red])

Thanks in advance from thge tip of Africa

5 Replies

# Re: Conditional Formatting

This should use a 'custom formula' under conditional formatting and that formula should be something like:
=\$D1=0
that assumes the 'Applied To' range is A1:Axxx and the closing balance is in column D

basically the \$ isn't needed but if you wanted the whole row to get highlighted or multiple cells on that row, then the \$ would be needed.

If your applied to range starts on row 2 then =\$D2=0  (basically the row listed in the formula should match the first cell in the applied to range.

# Re: Conditional Formatting

Thanks for your reply but I can't get it to print 'Apple' in red

# Re: Conditional Formatting

HI Mtarler

I can get the formula to operate on a single line but when I apply it to a column nothing works. Using =\$D\$1:\$D25=0

best response confirmed by DON4711 (Copper Contributor)
Solution

# Re: Conditional Formatting

correct that is the problem. The formula is ONLY \$D1=0. Basically the formula is based on ONLY the top left most cell. As Excel moves to other cells down or to the right it will internally update the formula ranges accordingly. So \$D1 means always use column D but as Excel looks down at row 2 it will also use \$D2 and some for row 3,4,.... but that is all internal. Write the formula as if you are doing it for the upper left cell ONLY but plan on copying/filling down/right.

# Re: Conditional Formatting

Thank you! I finally got it to work after much tearing out of what little hair is left. Column C is 'Name' and Column L is the 'Nil Value' condition.

1 best response

Accepted Solutions
best response confirmed by DON4711 (Copper Contributor)
Solution

# Re: Conditional Formatting

correct that is the problem. The formula is ONLY \$D1=0. Basically the formula is based on ONLY the top left most cell. As Excel moves to other cells down or to the right it will internally update the formula ranges accordingly. So \$D1 means always use column D but as Excel looks down at row 2 it will also use \$D2 and some for row 3,4,.... but that is all internal. Write the formula as if you are doing it for the upper left cell ONLY but plan on copying/filling down/right.