Change Cell Color Based on Color Index and Value

[TheCastillian]

Hi all,

I could use a hand with this one.

I have tested this on a single cell and it works great, however, I want to apply this to an entire sheet. ie Range(A1:BP65). With that said, A1 on the code is set as Today() and the intent is to run the code to determine what cells are passed due. I want to identify if any of the cells in the range are passed due and are not green (46).

Sub CellColor()
'This formula will change the color of cell that is passed due and not complete
'Color 43 is the green color that is used to signify that a job is complete
'Color 6 is yellow and will signify that a task is late
If Range("A2") < Range("A1") Then
If Range("A2").Interior.ColorIndex <> 43 Then
Range("A2").Interior.ColorIndex = 6
End If
End If
End Sub

Thanks in advance...

 

[CalcSux78]

I'm confused by the layout of your worksheet, but this may point you in the right direction

starting with:

Excel 2010
	A	B	C	D	E
1	5/19/2016
2	5/15/2016	5/20/2016	5/16/2016	5/18/2016	5/16/2016
3	5/19/2016	5/18/2016	5/20/2016	5/19/2016	5/16/2016
4	5/18/2016	5/20/2016	5/21/2016	5/16/2016	5/15/2016
5	5/17/2016	5/16/2016	5/19/2016	5/17/2016	5/20/2016
6	5/15/2016	5/15/2016	5/20/2016	5/19/2016	5/15/2016
Sheet1

Cell Formulas
Range	Formula
A1	=TODAY()

Run this:

Sub CellColor2()Dim iDone As Integer
Dim iLate As Integer
Dim dtDUE As Date
Dim cel As Range
Dim rng As Range


iDone = 43      'green
iLate = 6       'yellow
Set rng = Range("A2:E6")
dtDUE = Range("A1").Value


For Each cel In rng
    If cel < dtDUE Then
        With cel.Interior
            If Not .ColorIndex = iDone Then .ColorIndex = iLate
        End With
    End If
Next cel


End Sub

Gives you this:

Excel 2010
	A	B	C	D	E
1	5/19/2016
2	5/15/2016	5/20/2016	5/16/2016	5/18/2016	5/16/2016
3	5/19/2016	5/18/2016	5/20/2016	5/19/2016	5/16/2016
4	5/18/2016	5/20/2016	5/21/2016	5/16/2016	5/15/2016
5	5/17/2016	5/16/2016	5/19/2016	5/17/2016	5/20/2016
6	5/15/2016	5/15/2016	5/20/2016	5/19/2016	5/15/2016
Sheet1

Cell Formulas
Range	Formula
A1	=TODAY()

 

[TheCastillian]

CalcSux- that is EXACTLY what I needed. you are the man. This works perfect, I guess I was chasing my tail.

Thank you for your time on this!!!!