format cells between values -- MANY ranges, similar to icon sets

Hi acp,

Excel has a palette of 56 colors that you could choose from, they are invoked by their color index.

Palette.jpg

You could use a select case or a list of criteria and associated indices

Hi acp,

Try this:

Sub ACPLesCoulours(): Dim CI, n As Long, C As Range, R(1 To 20) As Range
CI = Array(0, 8, 45, 4, 6, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2)
For Each C In Range("K2:K" & Range("K" & Rows.Count).End(xlUp).Row)
If C = "" Then GoTo GetNext
Select Case C
Case 0 To 49: n = 1
Case 49 To 100: n = 2
Case 100 To 999: n = 3
Case 999 To 2999: n = 4
End Select
If R(n) Is Nothing Then _
Set R(n) = C Else Set R(n) = Union(R(n), C)
GetNext: Next C
For n = 1 To 20
If Not R(n) Is Nothing Then _
R(n).Interior.ColorIndex = CI(n)
Next n
End Sub

*You'll need to alter the red code according to your specs

Directions for running the routine(s) just supplied

If you haven't used macros before you'll need to go to:
File- options - trust center -trust center settings - macro settings ,
the second option down (disable all macros with notification)

Then - Copy the code to the clipboard

Open your Workbook

Press ALT + F11 to open the Visual Basic Editor.

Select "Module" from the Insert menu

Type "Option Explicit" then paste the code under it

With the cursor between Sub and End Sub press F5 (F8 to Single Step)

OR

Press ALT + Q to close the code window.

Press ALT + F8 then double click on the macro name
*Be sure to save the book with the code as Macro-Enabled

You're welcome and thanks for the rep!

About ten minutes to type, about a night's sleep to figure what to type