[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 74
  • Last Modified:

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.

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

0
Omar Hernandez
Asked:
Omar Hernandez
  • 15
  • 13
1 Solution
 
Martin LissOlder than dirtCommented:
Can you supply a sample workbook?
0
 
Omar HernandezAuthor Commented:
Thanks for your response Martin Liss,

 attachment.
completed-9-1--2---2-.xlsm
0
 
Martin LissOlder than dirtCommented:
What is bgW?
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Omar HernandezAuthor 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.
0
 
Martin LissOlder than dirtCommented:
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.
0
 
Omar HernandezAuthor Commented:
Right on, yes this is what i mean Martin Liss.
0
 
Omar HernandezAuthor Commented:
L = #FF99CC - rgb(255,153,204)
Z = #CCFFFF - rgb(204,255,255)
0
 
Martin LissOlder than dirtCommented:
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

0
 
Omar HernandezAuthor 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.
0
 
Martin LissOlder than dirtCommented:
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?
0
 
Omar HernandezAuthor 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.
0
 
Omar HernandezAuthor 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.
0
 
Martin LissOlder than dirtCommented:
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?
0
 
Omar HernandezAuthor 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.
0
 
Martin LissOlder than dirtCommented:
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?
0
 
Omar HernandezAuthor 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.
0
 
Martin LissOlder than dirtCommented:
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?
0
 
Omar HernandezAuthor 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
0
 
Martin LissOlder than dirtCommented:
OK that helps a lot. I'll get back to you.
0
 
Omar HernandezAuthor Commented:
Glad it help friend
0
 
Martin LissOlder than dirtCommented:
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?
0
 
Omar HernandezAuthor Commented:
A through L sounds great
0
 
Martin LissOlder than dirtCommented:
A through L
There's noting in K and L. Did you mean A through J?
0
 
Omar HernandezAuthor Commented:
yes i am aware that there is nothing, it is in progress to add additional words for those column.
0
 
Omar HernandezAuthor 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".
0
 
Martin LissOlder than dirtCommented:
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

0
 
Omar HernandezAuthor Commented:
Work's like a charm, Thanks a mil for your help and your time Martin Liss
0
 
Martin LissOlder than dirtCommented:
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
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 15
  • 13
Tackle projects and never again get stuck behind a technical roadblock.
Join Now