Conditional formatting (more than 3) in Excel

Conditional formatting in Office software like Excel may appear difficult for starters but thanks to the self-explanatory solution guide below, this should be a lot easier. It explains how to apply conditional formatting three times in Excel. People like to change the background colors which are possible only with macros or VBA. One can apply any color like purple, yellow, green to the Office software Excel workbook when each cell is programmed for high, medium and low scale with specific color codes. Copy and paste the equation below for conditional formatting in the required cell. The conditional formatting must be included in the 1st cell, resulting in formatting in adjacent cells.

Conditional formatting (more than 3) in Excel

Issue

I am trying to apply more than three options to Excel that changes the background color of the cell.
My range is R7:R1000. I need a VBA or macro that will change the background color of the cell as follows:
If the cell reads "Extreme", change background to red
If the cell reads "High" change background to Purple
If the cell reads "Medium", change background to yellow
If the cell reads "Low" change background to green
If the cell is empty, do nothing
Any assistance would be much appreciated!!

Solution

• You could try this:

in the cell you want to color put the cond format based upon the first cell that will result in the adjacent cells being colored.

=COUNTIF(AE10,"HD") + COUNTIF(AE10,"H") + COUNTIF(AE10,"T1")+ COUNTIF(AE10,"T2") + COUNTIF(AE10,"T3") + COUNTIF(AE10,"T4")+ COUNTIF(AE10,"T5")+ COUNTIF(AE10,"T6")+ COUNTIF(AE10,"T7")+ COUNTIF(AE10,"T7.5") 

or the shorter version;

=SUMPRODUCT(--(LEFT(J18,1)="H")) + SUMPRODUCT(--(LEFT(J18,1)="T")) 

• Just copy and paste the format into the cells you want to cond format!

Note

Solved by Steve

See also

Knowledge communities.

• Forum
• Excel forum
• Office software forum
• Programming forum

This document entitled « Conditional formatting (more than 3) in Excel » from Kioskea (en.kioskea.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.