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

Omar Hernandez
Omar Hernandez used Ask the Experts™
on
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

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Can you supply a sample workbook?

Author

Commented:
Thanks for your response Martin Liss,

 attachment.
completed-9-1--2---2-.xlsm
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
What is bgW?
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Author

Commented:
New at coding, had this created while getting information from youtube, umm it might be when click on cell highlight a certain part of a row in yellow or part of this function.
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
What the code in lines 15 to 35 does is to set the ACTIVE, ON DECK, etc rows to certain colors, but the rows are already that color so nothing changes.

From your question I think what you are saying is that if a cell in column H is equal to L then you want that row to be Mid Light Red and if it's Z then you want that row to be Mid Light Blue. Is that correct? If so please tell me the RGB values for those two colors.

Author

Commented:
Right on, yes this is what i mean Martin Liss.

Author

Commented:
L = #FF99CC - rgb(255,153,204)
Z = #CCFFFF - rgb(204,255,255)
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Replace your code above with this. Note that the rows will change color when you select a cell. The code that I added could be moved to the Worksheet_Change event where the row would change color when the cell in column changes to L or Z.

You also might want to read my A Guide to Writing Understandable and Maintainable VBA Code article.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ' You should always Dim your variables. If you place "Option Explicit" (without the quotes)
    ' at the top of the module you will be required to do that. It may be a pain but it will
    ' not allow spelling mistakes that cause bugs thyat are difficult to find.
    Dim lngLastRow As Long
    Dim lngRow As Long
    
    lngLastRow = Range("A1048576").End(xlUp).Row
    
    Application.ScreenUpdating = False
    
            Dim rownumber As Integer

            rownumber = ActiveCell.Row

            If Application.Intersect(ActiveCell, [headers]) Is Nothing Then
               If ActiveCell.Value <> "" Then
                  Range("a1:i5000").Interior.ColorIndex = xlNone
                  Range("a" & rownumber & ":F" & 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 & ":J" & bgR).Interior.Color = bg
    Next r
    
    ' Color row if column "H" is L or Z
    For lngRow = 5 To lngLastRow
        Select Case UCase(Cells(lngRow, "H"))
            Case "L"
                Range("A" & lngRow & ":J" & lngRow).Interior.Color = RGB(255, 153, 204) 'Mid Light Red
            Case "Z"
                Range("A" & lngRow & ":J" & lngRow).Interior.Color = RGB(204, 255, 255) 'Mid Light Blue
        End Select
    Next
                
                
'           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

Open in new window

Author

Commented:
Hey there Martin it is given the "L" and The "Z" in the H column the desired color, but when i click on them it does not show the row in the H COLUMN that has ether a "L" or a "Z" the color yellow on top of that color. Look at pic 2 from the this original post.
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I see in the first picture that the "yahoo" row has been changed to light red because the cell in column H is "L" and I see that in the second picture it has turned Yellow, but I don't understand when you want that to happen. Also at some point do you want it to turn back to light red? If so when?

Author

Commented:
Show it in yellow the row of the cell that it is clicked. If i click on other cell only show the row in yellow on the exact row of the cell that it is clicked. Hope this is clearer.

Author

Commented:
Before everything thanks for your help and your progress in getting this code to work the way i am looking for it to work.

***So in other words if i click on a cell that has "L" or "Z" on the H COLUMN have it to work so that even if they are assign a color show yellow on top of that color for that row if a cell is clicked.
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Sorry I still don't fully understand. With my current code if you click in any cell if a row where the cell in column H is "L", the row will change to light red. Is that what you want, or do you just want it to change to light red only if the cell in column H is clicked?

Author

Commented:
The color that you have assign to it for on column H for any letter that is found "L" or "Z" this part is working great.

Last part is
*** If i click on a cell that has a color for those in "L" or "Z" have that row in yellow if i click on another cell have it revert to the exact color it had and highlight in yellow the other row clicked.

This part is working normal keep on having this function.
clicking-on-those-that-have-already-.png
but if i have other's that have a color assign to it, if clicked have yellow showing on top of that color.
Clicking-on-those-that-have-already-.png
Clicking-on-those-that-have-already-.png
Clicking-on-those-that-have-already-.png
Hope by adding these pic you may see it much clearer.
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Let's assume that cell H9 is = "L". Are you saying that if I click anywhere in that row that the row should change to light red and then if I click anywhere in that row it should turn yellow and if I click it again it should be light red again?

Author

Commented:
Almost, assuming H9 is = "L" when any cell in that row is clicked it will turn yellow and if i click another cell on a different row like H12 = "Z" (H9 = "L" will revert to the assign color) and make the new row of H12 = "Z" turn to yellow because there is a present clicked cell on that row.
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Almost, assuming H9 is = "L" when any cell in that row is clicked it will turn light red and if i click another cell on a different row like H12 = "Z" (H9 = "L" will revert to the assign color) and make the new row of H12 = "Z" turn to yellow because there is a present clicked cell on that row.
Is that what you meant to say?

Author

Commented:
Hey Martin liss created a video to explain better

check this out and thanks

http://www.screencast.com/users/Omarantonio1231/folders/color/media/e8f1ec26-cd34-4f2c-84c1-ce032c18a81e
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
OK that helps a lot. I'll get back to you.

Author

Commented:
Glad it help friend
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Your code that turns cells in a row to Yellow, only goes from columns A through F. Is that what you want for yellow, or do you want A through J?

Author

Commented:
A through L sounds great
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
A through L
There's noting in K and L. Did you mean A through J?

Author

Commented:
yes i am aware that there is nothing, it is in progress to add additional words for those column.

Author

Commented:
If you see the actual video that i have sent you on screencast you can see that this clip shows the file being demonstrated with "A" column to "L".
Older than dirt
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
Try this. Notice line 8. That's a constant representing the last data column that you can change as you add more columns.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ' You should always Dim your variables. If you place "Option Explicit" (without the quotes)
    ' at the top of the module you will be required to do that. It may be a pain but it will
    ' not allow spelling mistakes that cause bugs thyat are difficult to find.
    Dim lngLastRow As Long
    Dim lngRow As Long
    ' NOTE
    Const LAST_COL = "J"
    
    lngLastRow = Range("A1048576").End(xlUp).Row
    
    Application.ScreenUpdating = False
    
            Dim rownumber As Integer

            rownumber = ActiveCell.Row

            ' If it's not a header row, turn cells in the clicked row yellow when a non-blank
            ' cell in the row is clicked
            If Application.Intersect(ActiveCell, [headers]) Is Nothing Then
               If ActiveCell.Value <> "" Then
                  Range("a1:" & LAST_COL & "5000").Interior.ColorIndex = xlNone
                  Range("a" & rownumber & ":" & LAST_COL & 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 & ":" & LAST_COL & bgR).Interior.Color = bg
    Next r
    
    ' Color row if column "H" is L or Z
    For lngRow = 5 To lngLastRow
        Select Case UCase(Cells(lngRow, "H"))
            Case "L"
                Range("A" & lngRow & ":" & LAST_COL & lngRow).Interior.Color = 13408767 'RGB(255, 153, 204) Medium Light Red
            Case "Z"
                Range("A" & lngRow & ":" & LAST_COL & lngRow).Interior.Color = 16777164 'RGB(204, 255, 255) Medium Light Blue
        End Select
    Next
                
    Select Case ActiveCell.Interior.Color
        Case 13408767, 16777164
            ' It's light red or light blue so change it to yellow
            Range("A" & ActiveCell.Row & ":" & LAST_COL & ActiveCell.Row).Interior.Color = 655359 ' RGB(255, 255, 9) Yellow
    End Select
    
                
'           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

Open in new window

Author

Commented:
Work's like a charm, Thanks a mil for your help and your time Martin Liss
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
You're welcome and I'm glad I was able to help.

Expand my profile’s “Full Biography” and you'll find links to some articles I've written that may interest you.

Marty - Microsoft MVP 2009 to 2016
              Experts Exchange MVE 2015
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2015

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial