troubleshooting Question

Search for letter on "H" column, have it highlighted in a color and if that color is clicked have the color display in yellow.

Avatar of Omar Hernandez
Omar Hernandez asked on
Microsoft ExcelVBAVB Script
28 Comments1 Solution132 ViewsLast Modified:
Hello Experts Exchange users and life savers. I have this code that works with other colors and wanted to add other function, but with same concept.

Search for a letter on the (H column) being "L" & "Z"
Display them with these colors
***"L" = Mid Light Red
***"Z" = Mid Light Blue
But when click even if it is highlighted with the color given
Show yellow on top of it.

Thanks for your time and help guys.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.ScreenUpdating = False
            Dim rownumber As Integer

            rownumber = ActiveCell.Row

            If Application.Intersect(ActiveCell, [headers]) Is Nothing Then
               If ActiveCell.Value <> "" Then
                  Range("a1:L5000").Interior.ColorIndex = xlNone
                  Range("a" & rownumber & ":L" & rownumber).Interior.Color = RGB(255, 255, 9)
               End If
            End If

    For r = 1 To 4
        Select Case r
            Case 1
                bgW = "ACTIVE"
                bg = RGB(255, 0, 0)     '   FF  00  00
            Case 2
                bgW = "ON DECK"
                bg = RGB(255, 102, 0)   '   FF  66  00
            Case 3
                bgW = "ON HOLD"
                bg = RGB(153, 102, 0)   '   99  66  00
            Case 4
                bgW = "COMPLETED"
                bg = RGB(0, 153, 51)     '   00  99  33

        End Select
        bgR = 0
        On Error Resume Next
            bgR = Application.Match(bgW, ActiveSheet.Range("C:C"), 0)
        If bgR > 0 Then Range("A" & bgR & ":L" & bgR).Interior.Color = bg
    Next r

'           check cells for desired format to trigger the routine
'           otherwise exit the sub
            Dim DateFormats, DF
            DateFormats = Array("m/d/yy;@", "m/d/yy")
            For Each DF In DateFormats
                If DF = Target.NumberFormat Then
                   If CalendarFrm.HelpLabel.Caption <> "" Then
                      CalendarFrm.Height = 191 + CalendarFrm.HelpLabel.Height
                      CalendarFrm.Height = 191
                   End If
                End If
    Application.ScreenUpdating = True
End Sub
Join our community to see this answer!
Unlock 1 Answer and 28 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 28 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros