Link to home
Start Free TrialLog in
Avatar of Omar Hernandez
Omar Hernandez

asked on

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

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.

User generated imageUser generated image
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

Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Can you supply a sample workbook?
Avatar of Omar Hernandez
Omar Hernandez

ASKER

Thanks for your response Martin Liss,

 attachment.
completed-9-1--2---2-.xlsm
What is bgW?
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.
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.
Right on, yes this is what i mean Martin Liss.
L = #FF99CC - rgb(255,153,204)
Z = #CCFFFF - rgb(204,255,255)
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

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.
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?
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.
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.
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?
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.
User generated image
but if i have other's that have a color assign to it, if clicked have yellow showing on top of that color.
User generated image
User generated image
User generated image
Hope by adding these pic you may see it much clearer.
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?
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.
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?
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
OK that helps a lot. I'll get back to you.
Glad it help friend
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?
A through L sounds great
A through L
There's noting in K and L. Did you mean A through J?
yes i am aware that there is nothing, it is in progress to add additional words for those column.
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".
ASKER CERTIFIED SOLUTION
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Work's like a charm, Thanks a mil for your help and your time Martin Liss
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