Previous Topic Next Topic

Conditional Fomatting with range values

Closed
vikassova Posts 1 Registration date Wednesday 4 December 2013 Status Member Last seen 5 December 2013 - 5 Dec 2013 à 00:03
TrowaD Posts 2921 Registration date Sunday 12 September 2010 Status Contributor Last seen 27 December 2022 - 5 Dec 2013 à 11:02
Here is my scenario:

Column A has ranking from 1 to 5.
Column B has zone/ classification from 1 to 7.
Column C has ratings. These ratings are based on a range.
for example if ranking (A) is 2 and zone (b) is 5, the rating should be in between 3 and 3.9. If the ranking is 1 and zone is 2 the rating will be in between 1 and 1.9.

I would like to use conditional formating to highlight the rankings which are not in the range.

Please suggest.

1 response

Réponse 1 / 1
TrowaD Posts 2921 Registration date Sunday 12 September 2010 Status Contributor Last seen 27 December 2022 555
5 Dec 2013 à 11:02
Hi Vikassova,

I'm guessing your first example is a typo and should be between 2 and 4.9 or else it doesn't make much sense.

Wouldn't it be better if users can't input a 'wrong' value?

Top menu > Data > Validate...
Choose the last option from the first dropdown list and enter this formula:
=AND(C1>A1,C1<B1)

Otherwise use this formula in CF:
=AND(C1<=A1,C1>=B1)

Best regards,
Trowa

Similar discussions

Clear cell contents based on value of another cell range using VBA
Tbonekiller -
Tbonekiller -

6 responses
help me to add particular cells
mathewmunna -
mathew munna -

2 responses
How to use conditional format based on text?
Leanne99 -
Mooseman -

26 responses
Return Seq Values Until Blank value then return different set of seq values
lorieb63 -
TrowaD -

1 response
Name Values
brendanconlin -
TrowaD -

1 response