CAPTVSHARMA
09-02-2020, 10:11 AM
Hi
I am trying to get VBA to code a cell basis the value in it.
Example : If Cell contains any text such as “5E”, “5D”, “4E”, “5C”, “4D”,”3E” Than HIGHLIGHT CELL RED.
At the moment I have the following code written, which is working but is long winded. Any suggestions on how to abbreviate it ?
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
Dim MyRange As Range
Set MyRange = Worksheets("Data").Range("B1:B116")
For Each Cell In MyRange
If Cell.Value Like "Orange" Then
Cell.Interior.ColorIndex = 46
ElseIf Cell.Value Like "Red" Then
Cell.Interior.ColorIndex = 3
ElseIf Cell.Value Like "Green" Then
Cell.Interior.ColorIndex = 4
ElseIf Cell.Value Like "Yellow" Then
Cell.Interior.ColorIndex = 6
ElseIf Cell.Value Like "5E" Then
Cell.Interior.ColorIndex = 3
ElseIf Cell.Value Like "5D" Then
Cell.Interior.ColorIndex = 3
ElseIf Cell.Value Like "4E" Then
Cell.Interior.ColorIndex = 3
ElseIf Cell.Value Like "5C" Then
Cell.Interior.ColorIndex = 3
ElseIf Cell.Value Like "4D" Then
Cell.Interior.ColorIndex = 3
ElseIf Cell.Value Like "3E" Then
Cell.Interior.ColorIndex = 3
Else
'Cell.Interior.ColorIndex = xlNone
End If
Next
Application.EnableEvents = True
End Sub
Many thanks !
I am trying to get VBA to code a cell basis the value in it.
Example : If Cell contains any text such as “5E”, “5D”, “4E”, “5C”, “4D”,”3E” Than HIGHLIGHT CELL RED.
At the moment I have the following code written, which is working but is long winded. Any suggestions on how to abbreviate it ?
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
Dim MyRange As Range
Set MyRange = Worksheets("Data").Range("B1:B116")
For Each Cell In MyRange
If Cell.Value Like "Orange" Then
Cell.Interior.ColorIndex = 46
ElseIf Cell.Value Like "Red" Then
Cell.Interior.ColorIndex = 3
ElseIf Cell.Value Like "Green" Then
Cell.Interior.ColorIndex = 4
ElseIf Cell.Value Like "Yellow" Then
Cell.Interior.ColorIndex = 6
ElseIf Cell.Value Like "5E" Then
Cell.Interior.ColorIndex = 3
ElseIf Cell.Value Like "5D" Then
Cell.Interior.ColorIndex = 3
ElseIf Cell.Value Like "4E" Then
Cell.Interior.ColorIndex = 3
ElseIf Cell.Value Like "5C" Then
Cell.Interior.ColorIndex = 3
ElseIf Cell.Value Like "4D" Then
Cell.Interior.ColorIndex = 3
ElseIf Cell.Value Like "3E" Then
Cell.Interior.ColorIndex = 3
Else
'Cell.Interior.ColorIndex = xlNone
End If
Next
Application.EnableEvents = True
End Sub
Many thanks !