Change Cell Color When Value Reaches Defined Threshold

Tmoyer

New Member
Joined
Feb 17, 2005
Messages
9
Hello, I would like the cells on my sheet to change color when the values reach defined thresholds. There are 14 defined thresholds and each one requires a unique color when the value is reached. The values are a running total with a new cell for each month that represent equipment "hours". They increase monthly and the thresholds are scheduled maintenance. The hours can remain in any defined threshold for several months, but when the next threshold is reached, I would like the color to change. This will Trigger a maintenance call.


The colors are just a sample and really don't matter as long as each threshold is a different color:

Threshold #1 = 0 to 499 = N/A or White
Threshold #2 = 500 to 749 = Purple
Threshold #3 = 750 to 999 = Blue
Threshold #4 = 1000 to 1249 = Green
Threshold #5 = 1250 to 1499 = Yellow
Threshold #6 = 1500 to 1749 = Orange
Threshold #7 = 1750 to 1999 = Beige
Threshold #8 = 2000 to 2249 = Grey
Threshold #9 = 2250 to 2499 = Pink
Threshold #10 = 2500 to 2749 = Light Purple
Threshold #11 = 2750 to 2999 = Light Blue
Threshold #12 = 3000 to 3249 = Baby Blue
Threshold #13 = 3250 to 3499 = Military Green
Threshold #14 = 3500 and up = Aqua


I am perplexed and I certainly appreciate any assistance.
Thank you in advance.
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
What is the cell reference that holds the number of hours and what cell(s) do you want coloured?
 
Upvote 0
Colored Cells - One cell per week. I initially said month, but I meant to say, "week":
AI2 through CH2

Reference Cells:
CI1 = 500
CJ1 = 750
CK1 = 1000
CL1 = 1250
CM1 = 1500
CN1 = 1750
CO1 = 2000
CP1 = 2250
CQ1 = 2500
CR1 = 2750
CS1 = 3000
CT1 = 3250
CU1 = 3500
 
Upvote 0
Sorry, I am still confused.

The cells in range AI2:CH2 hold a value between 0 and 3500+ and you want those cells to change colour according to the value in them - value spread per colour as per your first post.

Is that correct?
 
Upvote 0
OK, try this:

Open the relevant workshheet.

Run this code to set the colour scheme (once you have run it and colours are there you can delete the macro):

Code:
Sub SetColours()

    [ci1].Interior.Color = RGB(216, 216, 216)
    [cj1].Interior.Color = RGB(100, 250, 200)
    [ck1].Interior.Color = RGB(0, 200, 150)
    [cl1].Interior.Color = RGB(100, 250, 100)
    [cm1].Interior.Color = RGB(0, 150, 0)
    [cn1].Interior.Color = RGB(150, 200, 230)
    [co1].Interior.Color = RGB(50, 150, 200)
    [cp1].Interior.Color = RGB(250, 250, 125)
    [cq1].Interior.Color = RGB(255, 250, 0)
    [cr1].Interior.Color = RGB(240, 150, 75)
    [cs1].Interior.Color = RGB(250, 120, 25)
    [ct1].Interior.Color = RGB(240, 75, 75)
    [cu1].Interior.Color = RGB(255, 0, 0)

End Sub

Then put this code in the worksheet module:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lHours As Long
lHours = Target.Value

If Not Intersect(Target, Range("ai2:ch2")) Is Nothing Then
    If lHours < 500 Then Target.Interior.Color = vbWhite
    If lHours > 499 And lHours < 750 Then Target.Interior.Color = [ci1].Interior.Color
    If lHours > 749 And lHours < 1000 Then Target.Interior.Color = [cj1].Interior.Color
    If lHours > 999 And lHours < 1250 Then Target.Interior.Color = [ck1].Interior.Color
    If lHours > 1249 And lHours < 1500 Then Target.Interior.Color = [cl1].Interior.Color
    If lHours > 1499 And lHours < 1750 Then Target.Interior.Color = [cm1].Interior.Color
    If lHours > 1749 And lHours < 2000 Then Target.Interior.Color = [cn1].Interior.Color
    If lHours > 1999 And lHours < 2250 Then Target.Interior.Color = [co1].Interior.Color
    If lHours > 2249 And lHours < 2500 Then Target.Interior.Color = [cp1].Interior.Color
    If lHours > 2499 And lHours < 2750 Then Target.Interior.Color = [cq1].Interior.Color
    If lHours > 2749 And lHours < 3000 Then Target.Interior.Color = [cr1].Interior.Color
    If lHours > 2999 And lHours < 3250 Then Target.Interior.Color = [cs1].Interior.Color
    If lHours > 3249 And lHours < 3500 Then Target.Interior.Color = [ct1].Interior.Color
    If lHours > 3449 Then Target.Interior.Color = [cu1].Interior.Color
End If

End Sub

Now when the values in range AI2:CH2 should change colour as the value changes from one threshold to another.
If you want to change the colour scheme at any time then all you need to do is alter the colours in range CI1:CU1, any new colours will be reflected in rangeAI2:CH2 when a cell in that range changes.

Hope this is something like what you want!
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,114,002
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top