MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Conditional Formating Questions


Posted by Glenn Balian on January 29, 2002 2:21 PM

I'm fine with conditional formatting for the cell that calculates the data. For this cell I can use the "cell value" option. However, I want to have another cell in the summary portion of the report with the same color as the cell with the calculation.

BEST OPTION
For the linked cell, I want to turn a wingding "L" (a circle) the same color as the calculated cell. I'm working with three colors. Here are the contraints:
Calculated variance is Less than -20% or Greater than +20% (code red); Variance is -10% to -20% OR +10% to +20% (code yellow); Variance is between -10% and +10%.
Again the cell value constraints allow me to get the job done for the calculated cell. I can't accomplish this with the "formula is" choices. The key problem is that Excel won't take "-.2<C53<-1 or .1<C53<.2" for the yellow option.

SECOND OPTION
The other idea is being able to have the referred cell simply change colors with the conditionally formatted calculation cell. I haven't found out how to make this link happen.


Posted by Chris D on January 29, 2002 3:52 PM

Hi Glenn,

not sure if I'm on the right track here...but

can you not just use 3 conditions, each utilising the "formula is" option ?

so say your original cell is A1 :

where the 1st formula is : or(a1<-20%,a1>20%) format = wingding L font and font colour is red

where the 2st formula is : and(abs(a1)<20.00001%,abs(a1)>9.999991%) format = wingding L font and font colour is yellow

and the third is : and(a1>-10.00001%,a1<10.00001%)
format = wingding L font and font colour is whatever your third choice was

sorry, I haven't actually tested this, midnight is just a few minutes away here and I'm up way too late

Hope this gets you started though
Chris