Conditional Formatting with certain criterias

[max4asd]

Hi Guys,


I am struggling with conditional formating in excel 2010 were i want to color code the cells based on certain criterias for example:


A Class B Class C Class D Class HORECA KA Class

6 6 5 3 3 6


I want to color the cell were i have the numbers by chechking if A1 = A Class and if A2 = 6 color blue or below 6 color red,if A1 = B Class and if A2 = 6 color blue or below 6 color red,if A1 = C Class and if A2 = 5 color blue or below 5 color red,if A1 = D Class and if A2 = 3 color blue or below 3 color red,if A1 = Horeca and if A2 = 3 color blue or below 3 color red,if A1 = KA Class and if A2 = 6 color blue or below 6 color red.


How can i input this criteria for the cell were i have numbers but format based on the above text/class mentioned.


Thanks in Advance

 

[Hui]

Max4asd

I have assumed your data is in A1:F2

with the numbers in Row 2


Select A2:F2


On Home Tab

Conditional Formatting

Manage Rules

* New Rules

Use a Formula to determine which cells to Format

=AND(A1="A Class",A2>=6)

and select a Format Fill Color Blue

Ok when complete


Now repeat the steps from the * to ok with the following extra rules


=AND(A1="A Class",A2<6)

Format Fill Color Red


=AND(A1="B Class",A2>=6)

Format Fill Color Blue


=AND(A1="B Class",A2<6)

Format Fill Color Red


=AND(A1="C Class",A2>=5)

Format Fill Color Blue


=AND(A1="C Class",A2<5)

Format Fill Color Red


=AND(A1="D Class",A2>=3)

Format Fill Color Red


=AND(A1="D Class",A2<3)

Format Fill Color Blue


=AND(A1="Horecka",A2>=3)

Format Fill Color Blue


=AND(A1="Horecka",A2<3)

Format Fill Color Red


=AND(A1="KA Class",A2>=6)

Format Fill Color Blue


=AND(A1="KA Class",A2<6)

Format Fill Color Red


This will only work on Excel 2007+

Prior to Excel 2007 you are limited to 3 conditional formats and a default color without using vba