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.

search-letter-L-and-Z-give-them-a-co.pngsearch-letter-L-and-Z-give-them-a-co.png
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 calendarfrm.show 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
                   Else
                      CalendarFrm.Height = 191
                      CalendarFrm.Show
                   End If
                End If
            Next
            
    Application.ScreenUpdating = True
End Sub
ASKER CERTIFIED SOLUTION
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