 Highlighted

# Excel Assign Cell Color with Formula help

I have a spreadsheet where staff have a target in Column H, and each month they enter their accomplishment. I would like to create a formula that says:

If Column I is 95% of Column H, the cell is Green. If it is between 90.0% and 94.99 % the cell is yellow. If it is below 89.99%, the column is red.

I am having a terrible time figuring this out. I can manually calculate each of the rows/columns--but there are many, and I would like the formulas to work, as the targets in Column H change from year to year.

Can anyone help?

3 Replies
Highlighted

# Re: Excel Assign Cell Color with Formula help

@ShellyVrsek , you may apply conditional formatting rules to your I column range with formulas like

```=\$I1>=0.95*\$H1

=(\$I1>=0.9*\$H1)*(\$I1<0.95*\$H1)

=\$I1<0.9*\$H1```

accordingly to each rule and assuming your range starts from first row (or adjust otherwise)

Highlighted

# Re: Excel Assign Cell Color with Formula help

T@Sergei Baklan Thanks so much for your response. It seems like that should work, but it is not working properly. I'm not sure what I am doing wrong.

I have changed it to this formula:

=I8>=.95*\$H\$8

As, H8 will consistently be the target. It is not affecting the color, or always turning it red--even if the value is less. Some of my values are formatted as percentages--meaning the H8 is entered as a percentage, and so it so is the data entered in i7. Could that be the issue?

Highlighted

# Re: Excel Assign Cell Color with Formula help

If you always compare with \$H\$8 it looks like Perhaps you may modify attach sample to make it more close to your data