Question on Cell Color Based On Input

[tunacan]

Hello,

I am an Excel newbie and have exhausted what detail I could find on the web to help me debug this problem.

The code below works if you type the word 'ID' or 'Severity' in a cell, it changes to Purple with a yellow-ish font. It only works if you paste in the word 'ID' or 'Severity' etc.. in a cell after double-clicking that cell. Any thoughts ?

I populate columns A:L with a web query and am trying to Highlight the header row. The code I am refering to starts with 'Insert Color Hack' but I included the entire snippet which allows me to add multiple values per cell using a drop-down list.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
Dim icolor As Integer
Dim fcolor As Integer
If Target.Count > 1 Then GoTo exitHandler
On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler
If rngDV Is Nothing Then GoTo exitHandler
If Intersect(Target, rngDV) Is Nothing Then
'do nothing
Else
Application.EnableEvents = False
newVal = Target.Value
Application.Undo
oldVal = Target.Value
Target.Value = newVal
If Target.Column >= 1 Then
If oldVal = "" Then
'do nothing
Else
If newVal = "" Then
'do nothing
Else
Target.Value = oldVal _
& ", " & vbCrLf & newVal
End If
End If
End If
End If

' Insert Color Hack
If Not Intersect(Target, Range("A:L")) Is Nothing Then

Select Case Target
Case "ID", "Service", "Status", "Severity", "Resolution", "Description"
icolor = 21
fcolor = 45
Case Else
'Whatever
End Select
Target.Interior.ColorIndex = icolor
Target.Font.ColorIndex = fcolor
End If
' End Color Hack
exitHandler:
Application.EnableEvents = True
End Sub

 

[AlphaFrog]

Can I ask why you are using VBA code an not using Conditional Formatting for this?

• Select cell A1
• Select columns A to L
• Select from the menu Format\ Conditional Formatting
  • Condition 1:
  • Formula is =OR(A1="ID",A1="Service",A1="Status",A1="Severity",A1="Resolution",A1="Description")
  • Format your background color and font color
  • OK

 

[tunacan]

Duplicate post

 

[tunacan]

Thanks so much, that worked perfectly and I am extremely grateful for your help with this.

I knew that you could use FORMULA's with conditional formatting but was not aware of the syntax with the =OR statement.