Excel Conditional Formatting with more than 3 Colors in the scale

Question

Hi Everyone,

I'm working on a project that requires me to have specific colors for certain values. I'm curious as to how I would set up conditional formatting to add the color scale I've attached to the cells I need formatted. Using the normal "Format cells based on values" option doesn't allow me to have more than three colors.

Thanks in advance!

This is a project for a meteorology class where we have to create a month long record of temperature data for the following states.

Answer 1

You need to set up 13 separate CF rules, using the Formula option. Use formulas like this:

=AND(D3>=10,D3<20)

and then choosde the appropriate color for that band. Then copy that cell, and paste formats over all the others.

Answer 2

Hi Berine,

Is there a way I could make that a gradient? So a temperature of 47 would be closer to the color of 50?

Answer 3

Not easily. I'm not sure of the limit on the number of formula CFs, but you could try that with smaller steps.

OR - You could identify the color based on the integer temperature - I think you could create a table of temperature values with the color as the fill for each individual temperature (all 130+ possible values), and then use a macro to read that cell's RGB settings and apply it to the values in your table.

Answer 4

Yeah the thought of creating 130 CF formulas doesnt sound that appealing to me.

Do you have any resources on how I could create such a macro? My other concern with that would be finding all 130+ values.

Answer 5

The macro is below.

First, you need to assign your colors - I have only done three, but you need to do all 130 or 65 if you want to do it by 2 degrees, but that would require slightly different code....) Those three cells with color I have named "Colors"

Then select the Value cells, and run the macro, and this should be the result:

Here is the macro:

Option Explicit

Sub ColorTemperatureValues()

Dim rngC As Range 

Dim ci As Variant 

For Each rngC In Selection 

    ci = Application.Match(rngC.Value, Range("Colors"), False) 

    If Not IsError(ci) Then 

        rngC.Interior.Color = Range("Colors").Cells(ci).Interior.Color 

    End If 

Next 

End Sub

And here is a macro that will at least give you a cool to warm color gradient in 130 cells in column A:

Sub CoolToWarm()

Dim i As Long 

For i = 1 To 130 

    Cells(i, 1).Value = i - 11 

    Cells(i, 1).Interior.Color = RGB(2 \* i, 255, 255 - 1.5 \* i) 

Next i 

End Sub