Conditional Formatting

[DarbyBrown]

Running Excel 365, and trying to change the color of one of the cell’s background color depending on the value within the cell. The cell contains a currency number derived from the sum of a series of deposit values minus the sum of a series of withdrawals. I want to have the cell background to be either red if the value is less than 0 or green if it is equal to or greater than 0.

I know the way to do this is using Conditional Formatting and have tried the following 2 options::

Home>Conditional Formatting>New Rule>
Format all cells based on their value>
Format Style>2 color scale>

Option 1:

Minimum
Type>Number>
Value> <0
Color>’Dark Red’

Maximum
Type>Number>
Value> <0
Color> ‘Green’

Option 2:

Minimum
Type>Formula>
Value> ‘IF($H$17<0)
Color>’Dark Red’

Maximum
Type>Formula>
Value>’IF($H$17=>0)
Color> ‘Green’

Neither option worked. I now I can have the cell display (-$1234.00) but I want the cell to 'scream' deficit!! I'm not sure where to go from here. What did I do wrong?

darby

 

[JoeMo]

When you use a formula to set the condition(s) for conditional formatting, the formula must evaluate to TRUE or FALSE. So, for example, if you want H17 to be green if the value in it is greater than or equal to zero, try:
Home>Conditional Formatting>New Rule>Use a formula
and enter the formula: =H17>=0
then choose the format and click OK.

 

[DarbyBrown]

Conditional Formatting will not accept relative references in the formulas,(ie: =H17<0) you need to use absolute references (ie: =$H$7<0). See my option 2. At least in 365, not sure with previous versions.

darby

 

[FDibbins]

Conditional Formatting will not accept relative references in the formulas,(ie: =H17<0) you need to use absolute references (ie: =$H$7<0). See my option 2. At least in 365, not sure with previous versions.

darby

Actually, yes it will, I dont have 365, but I seriously doubt they would have changed that.
Did you use the "use Formula" option?

 

[JoeMo]

Conditional Formatting will not accept relative references in the formulas,(ie: =H17<0) you need to use absolute references (ie: =$H$7<0). See my option 2. At least in 365, not sure with previous versions.

darby

Did you try it?? Seriously doubt that any version of Excel will not accept relative references.

 

[DarbyBrown]

JoeMo, YES I did try it before I replied. I just did it again and got this error message.

"You cannot use relative references in Conditional Formatting criteria for color scales, data bars and Icon sets. "

You are right, I didn't think it would matter in this case either until I tried it. I use both Relative and Absolute references in this workbook so am no stranger to them. Relative reference was the original thing I tried when I first got this message so I set up the formula and clicked on Cell H17 and that's when it came up as the double absolute, $H$17. When that didn't work I was baffled. It's probably something very simple that I just can't see.

 

[JoeMo]

JoeMo, YES I did try it before I replied. I just did it again and got this error message.

"You cannot use relative references in Conditional Formatting criteria for color scales, data bars and Icon sets. "

You are right, I didn't think it would matter in this case either until I tried it. I use both Relative and Absolute references in this workbook so am no stranger to them. Relative reference was the original thing I tried when I first got this message so I set up the formula and clicked on Cell H17 and that's when it came up as the double absolute, $H$17. When that didn't work I was baffled. It's probably something very simple that I just can't see.

If you read post #2 carefully, you will see that it suggests using a new rule in the form of a formula, not "color scales, data bars or Icon sets".

 

[DarbyBrown]

You are so correct, I read it and it didn't register. Told you it was something simple, I used "Format all cells based on their value>" instead of "Use a formula to determine which cells to format." Made that change, and all is well with the world again. It works exactly as I intended it to, now that you straightened me out.

Thanks

darby

 

[DarbyBrown]

FDibbins;
No I didn't, see my last reply to JoeMo. That was the problem, I used the wrong formatting tool.

 

[FDibbins]

Good stuff As long as you got where you wanted to be