Solved

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

Posted on 2016-09-15
28
42 Views
Last Modified: 2016-09-16
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
Comment
Question by:Omar Hernandez
  • 15
  • 13
28 Comments
 
LVL 45

Expert Comment

by:Martin Liss
ID: 41801589
Can you supply a sample workbook?
0
 

Author Comment

by:Omar Hernandez
ID: 41801660
Thanks for your response Martin Liss,

 attachment.
completed-9-1--2---2-.xlsm
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 41801686
What is bgW?
0
 

Author Comment

by:Omar Hernandez
ID: 41801698
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
 
LVL 45

Expert Comment

by:Martin Liss
ID: 41801817
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
 

Author Comment

by:Omar Hernandez
ID: 41801982
Right on, yes this is what i mean Martin Liss.
0
 

Author Comment

by:Omar Hernandez
ID: 41801990
L = #FF99CC - rgb(255,153,204)
Z = #CCFFFF - rgb(204,255,255)
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 41802134
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
 

Author Comment

by:Omar Hernandez
ID: 41802338
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
 
LVL 45

Expert Comment

by:Martin Liss
ID: 41802372
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
 

Author Comment

by:Omar Hernandez
ID: 41802378
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
 

Author Comment

by:Omar Hernandez
ID: 41802384
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
 
LVL 45

Expert Comment

by:Martin Liss
ID: 41802385
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
 

Author Comment

by:Omar Hernandez
ID: 41802399
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 45

Expert Comment

by:Martin Liss
ID: 41802403
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
 

Author Comment

by:Omar Hernandez
ID: 41802408
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
 
LVL 45

Expert Comment

by:Martin Liss
ID: 41802413
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
 

Author Comment

by:Omar Hernandez
ID: 41802435
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
 
LVL 45

Expert Comment

by:Martin Liss
ID: 41802475
OK that helps a lot. I'll get back to you.
0
 

Author Comment

by:Omar Hernandez
ID: 41802481
Glad it help friend
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 41802482
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
 

Author Comment

by:Omar Hernandez
ID: 41802487
A through L sounds great
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 41802490
A through L
There's noting in K and L. Did you mean A through J?
0
 

Author Comment

by:Omar Hernandez
ID: 41802493
yes i am aware that there is nothing, it is in progress to add additional words for those column.
0
 

Author Comment

by:Omar Hernandez
ID: 41802512
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
 
LVL 45

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 41802573
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
 

Author Closing Comment

by:Omar Hernandez
ID: 41802587
Work's like a charm, Thanks a mil for your help and your time Martin Liss
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 41802607
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now