Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change color of row (Col A thru Col Q) based on text value in Col J
I am using David McRitchie's code for changing color of entire row
based on contents based on a specified cell text value: 'Target.EntireRow.Interior.ColorIndex = 36'. This works fine; however, I only want to change color in the first 17 cells in each of the affected rows. How do I do this? Also, I am confused: do I want the stmt 'Application.EnableEvents = True' at the top of my coding in the 'Worksheet_Change' event coding (occupies the Sheet1 Module)? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change color of row (Col A thru Col Q) based on text value in Col J
One way:
Target.EntireRow.resize(1,17).Interior.ColorIndex = 36 JingleRock wrote: I am using David McRitchie's code for changing color of entire row based on contents based on a specified cell text value: 'Target.EntireRow.Interior.ColorIndex = 36'. This works fine; however, I only want to change color in the first 17 cells in each of the affected rows. How do I do this? Also, I am confused: do I want the stmt 'Application.EnableEvents = True' at the top of my coding in the 'Worksheet_Change' event coding (occupies the Sheet1 Module)? -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change color of row (Col A thru Col Q) based on text value in ColJ
Dave,
Thanks for your response and suggestion. For some reason, my Worksheet_Change event is not working at all. For example, I commented-out my Sheet1 Module and replaced it with: Private Sub Worksheet_Change(ByVal Target As Range) Target.Interior.ColorIndex = 3 End Sub Then, I enter text in various cells of Sheet1 and there is zero color change. Any ideas? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change color of row (Col A thru Col Q) based on text value in ColJ
The Change Event is working now. Again, any ideas?
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change color of row (Col A thru Col Q) based on text value in Col J
My bet is that you didn't put the code in the correct module.
In excel, Rightclick on the worksheet tab that should have this behavior. Select view code paste your procedure into the code window that just opened. And make sure that macros are enabled and events are enabled, too. Inside the VBE: hit ctrl-g (to see the immediate window) type: application.enableevents = true and hit enter. Then back to excel to test. JingleRock wrote: Dave, Thanks for your response and suggestion. For some reason, my Worksheet_Change event is not working at all. For example, I commented-out my Sheet1 Module and replaced it with: Private Sub Worksheet_Change(ByVal Target As Range) Target.Interior.ColorIndex = 3 End Sub Then, I enter text in various cells of Sheet1 and there is zero color change. Any ideas? -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change color of row (Col A thru Col Q) based on text value in Col J
If you want to highlight other than the first to the 17th you could try
something like this. Highlights the row from column B to K which you can change by tweeking the code and only works in the Range("B8:K22") that is set to Data in the code, which you can also change. You can probably figure out the Offset/Resize changes to suit your chosen range. Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim Data As Range Dim i As Integer Dim j As Integer Dim k As Integer Dim cells As Range i = 2 j = 8 k = ActiveCell.Column() Set Data = Range("B8:K22") Data.Interior.ColorIndex = xlNone If ActiveCell.Row < 8 Or ActiveCell.Row 22 Or _ ActiveCell.Column < 2 Or ActiveCell.Column 11 Then Exit Sub End If ActiveCell.Offset(0, -(k - i)). _ Resize(1, 10).Interior.ColorIndex = 36 '26 End Sub HTH Regards, Howard "JingleRock" wrote in message ... I am using David McRitchie's code for changing color of entire row based on contents based on a specified cell text value: 'Target.EntireRow.Interior.ColorIndex = 36'. This works fine; however, I only want to change color in the first 17 cells in each of the affected rows. How do I do this? Also, I am confused: do I want the stmt 'Application.EnableEvents = True' at the top of my coding in the 'Worksheet_Change' event coding (occupies the Sheet1 Module)? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I change cell color in excel based on text input? | New Users to Excel | |||
Change Text Color in one cell based upon entry in referenced cell | Excel Discussion (Misc queries) | |||
Want to change color based on Text, NOT using Conditional Formatin | Excel Programming | |||
How to code so cells or text automatically change color based on . | Excel Worksheet Functions | |||
change text color based on logical test | Excel Worksheet Functions |