Found code elsewhere, and tweaked it, that allows me to do some simple conditional formatting to alter the colour of a cell in column G depending on the RAG status entry inserted into a cell (Red,Amber,Green,Complete,N/A). The cells in column G have a drop down validation list.
I'm sure that there is a more elegant way of doing this, but it works for me at the moment. However when I insert a row I get an Run-time error '13': Type mismatch message, flagging theline of code.Case "Red"
I've tried various things but can't figure it out, but can't get to the bottom of it.
The code is attached below
Workbook attached also.Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
If Not Intersect(Target, Range("G:G")) Is Nothing Then
Select Case Target
Case "Red"
icolor = 3
Case "red"
icolor = 3
Case "Amber"
icolor = 45
Case "amber"
icolor = 45
Case "Green"
icolor = 4
Case "green"
icolor = 4
Case "Complete"
icolor = 32
Case "complete"
icolor = 32
Case "N/A"
icolor = 15
Case "Not Applicable"
icolor = 15
Case "n/a"
icolor = 15
Case Else
'Whatever
End Select
Target.Interior.ColorIndex = icolor
End If
End Sub
Bookmarks