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.
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.
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
Can you supply a sample workbook?
ASKER
What is bgW?
ASKER
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.
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.
ASKER
Right on, yes this is what i mean Martin Liss.
ASKER
L = #FF99CC - rgb(255,153,204)
Z = #CCFFFF - rgb(204,255,255)
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.
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
ASKER
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?
ASKER
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.
ASKER
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.
***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?
ASKER
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.
but if i have other's that have a color assign to it, if clicked have yellow showing on top of that color.
Hope by adding these pic you may see it much clearer.
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.
but if i have other's that have a color assign to it, if clicked have yellow showing on top of that color.
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?
ASKER
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?
ASKER
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
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.
ASKER
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?
ASKER
A through L sounds great
A through LThere's noting in K and L. Did you mean A through J?
ASKER
yes i am aware that there is nothing, it is in progress to add additional words for those column.
ASKER
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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