vba conditional formatting

[kevvukeka]

Hi All.

I have to insert conditional formatting in an excel and I chose to do it through VBA. In the attached excel sheet in COl N there is a formula that would calculate the no of days between two given dates. I have to assign different color when no of days in column N exceeds a limit. I would add fresh data everyday after the last row in the given data. I tried using the below code but I get "Type mismatch error". Can someone plz check and help me what is missing here.





Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long

If Not Intersect(Target, Range("N:N")) Is Nothing Then




Select Case Target
Case 31 To 60
Target.Interior.ColorIndex = 10213316

Case 61 To 90
Target.Interior.ColorIndex = 14857357

Case 91 To 110
Target.Interior.ColorIndex = 9420794

Case 111 To 1000
Target.Interior.ColorIndex = 5197823

Case 0 To 30
Target.Interior.ColorIndex = xlNone

End Select



End If
End Sub

[Bob Phillips]

This all seems a bit confused to me. Column N is a formula, so you shouldn't be directly changing the value, you change the value in L, M or Q to drive this change. But by doing that, you will not trigger the worksheet change event for column N. And even if you do change the value in N directly, I get a subscript out of range error, not Type mismatch, and that is because Colorindex can only be a value between 1 and 56, or xlColorindexAutomatic or xlColorindexNone, noit the huge values you have (which look more like Color than Colorindex).