Auto color-shading of rows

  • Thread starter Thread starter Fitz
  • Start date Start date
F

Fitz

Can I automate an excel worksheet such that if ,say, "436" is written in a
certain cell it shades that row yellow, or if "437" is in that cell it
shades the row blue. It would then be easy to visually tell in an excel
chart which lines belonged to ,say, specific companies.
 
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case 436: .EntireRow.Interior.ColorIndex = 6
Case 437: .EntireRow.Interior.ColorIndex = 5
'etc
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--

HTH

RP
(remove nothere from the email address if mailing direct)
 
fitz1 said:
Can I automate an excel worksheet such that if ,say, "436" is written in a
certain cell it shades that row yellow, or if "437" is in that cell it
shades the row blue. It would then be easy to visually tell in an excel
chart which lines belonged to ,say, specific companies.
Click to expand...
In addition to a programmatic solution, you may also want to check out
conditional formatting (Format | Conditional Formatting...)

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
I appreciate the feedback but I don't understand this code at all. In
reality, I'm looking for 4 different color scenarios: if "424" is written in
a cell in a specific column of an excel worksheet then the row, in its
entirety, associated with that "424"cell will be highlighted in YELLOW, if
"426" is written in that same column in any particular cell then the
complete row associated with that cell would be hightlighted in LIGHT GREEN,
if "436" is written in that same column in any particular cell then the
complete row associated with that cell would be hightlighted in LIGHT BLUE,
if "TAU" is written in that same column in any particular cell then the row
associated with that cell would be hightlighted in LIGHT ORANGE. Will this
macro work on each worksheet in the workbook? Appreciate your feedback.
Fitz
 
It will with a slight modification. This will work on any change in column M
(13)

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Target.Column = 13 Then
With Target
Select Case .Value
Case 424: .EntireRow.Interior.ColorIndex = 6
Case 426: .EntireRow.Interior.ColorIndex = 35
Case 436: .EntireRow.Interior.ColorIndex = 41
Case "TAU": .EntireRow.Interior.ColorIndex = 45
'etc
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Doesn't seem to be working. Can't explain. I'm working in Excel 2003. If
I
right click the worksheet thats relevant to the request (it's also the one
maximized), I get the option to "View Code". If I then left-click "View
Code", I end up in Microsoft Visual Basic and I have a window for sheet 1
which contains two drop-down boxes: The left one has (General) and
Worksheet as the 2 dropdown choices and the right one has 10 choices
including selectionchange and activate, to name two. Where specifically
should I paste this and does it effect only that sheet or all other sheets
as well in that workbook. I only want it to effect one worksheet in the
workbook. I also don't want it to be run as regular code in other workbooks
every time I open excel.
In any case, I then copy and paste the code (as shown below) you've
provided in its entirety and press enter. When I then get back into Excel it
questions me about adjusting my security settings to allow a macro to run. I
do this but it still does not work. No hightlighting of lines as desired.
Any more advice or ideas would be greatly appreciated. Please be specific as
I'm in over my head when it comes to code and Microsoft VBA.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Target.Column = 13 Then
With Target
Select Case .Value
Case 424: .EntireRow.Interior.ColorIndex = 6
Case 426: .EntireRow.Interior.ColorIndex = 35
Case 436: .EntireRow.Interior.ColorIndex = 41
Case "TAU": .EntireRow.Interior.ColorIndex = 45
'etc
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub
 
Fitz said:
Doesn't seem to be working. Can't explain. I'm working in Excel 2003. If
I
right click the worksheet thats relevant to the request (it's also the one
maximized), I get the option to "View Code". If I then left-click "View
Code", I end up in Microsoft Visual Basic and I have a window for sheet 1
which contains two drop-down boxes: The left one has (General) and
Worksheet as the 2 dropdown choices and the right one has 10 choices
including selectionchange and activate, to name two. Where specifically
should I paste this
Click to expand...

Just paste the code into the big blank area of the window that shows. The
dropdowns can be ignored here.
and does it effect only that sheet or all other sheets
as well in that workbook. I only want it to effect one worksheet in the
workbook. I also don't want it to be run as regular code in other workbooks
every time I open excel.
Click to expand...

I gave you code for a single sheet first time, then for all sheets after
your follow-up question.
If you want just one sheet, you have the wrong code, use this version
instead

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case 436: .EntireRow.Interior.ColorIndex = 6
Case 437: .EntireRow.Interior.ColorIndex = 5
'etc
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub
 
Just to get this all back on track, because I copied and pasted wrong code,
do I now have to delete any code that I have in there already? If so, where
do I delete?Secondly, on the one sheet that I'm interested in getting the
rows to be highlighted (tabbed "CC130"), the column of interest is column F.
In column F there will be four possible entries as outlined below:

-if "424" is written in a cell in column F then the row, in its entirety,
associated with that "424"cell will be highlighted in YELLOW
- if "426" is written in that same column in any particular cell then the
complete row associated with that cell would be hightlighted in LIGHT GREEN,
-if "436" is written in that same column in any particular cell then the
complete row associated with that cell would be hightlighted in LIGHT BLUE,
-if "TAU" is written in that same column in any particular cell then the row
associated with that cell would be hightlighted in LIGHT ORANGE.

I only need this highlighting to work on this one particular worksheet in
the workbook.
When I insert any code it appears to be viewing this as a macro and Excel
wants me to enable macros. How do I tell excel that this macro is written by
a trusted publisher so it will run behind the scenes everytime I open this
file without asking me to enable macros all over again?
Please just restate the exact code required for this scenario because the
latest does not seem to address a specific column (ie. F or 6) nor does it
include two of the four possible cell input possibilities listed above.

Thanks for your patience.
 
Fitz,

Remove this code

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Target.Column = 13 Then
With Target
Select Case .Value
Case 424: .EntireRow.Interior.ColorIndex = 6
Case 426: .EntireRow.Interior.ColorIndex = 35
Case 436: .EntireRow.Interior.ColorIndex = 41
Case "TAU": .EntireRow.Interior.ColorIndex = 45
'etc
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


replace it with this code

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
With Target
If .Column = 6 Then
Select Case .Value
Case 436: .EntireRow.Interior.ColorIndex = 6
Case 437: .EntireRow.Interior.ColorIndex = 5
'etc
End Select
End If
End With

ws_exit:
Application.EnableEvents = True
End Sub

The only way you can avoid the message is to set your security setting to
low, which I don't advise, or digitally sign it. I haven't done this, but it
is described at

Note that certification only works in Excel 2000 and later.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnoxpta/html/odc_dsvba.asp
Code Signing Office XP Visual Basic for Applications Macro Projects

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnimo00/html/combat.asp
Combat Macro Viruses with Digital Signatures

Another reference:
http://msdn.microsoft.com/library/en-us/odeopg/html/deovrsigningvbaproject.asp

http://office.microsoft.com/assistance/2002/articles/otelldigitalcertiftrustworthy.aspx
How to Tell if Digital Certificate Is Trustworthy in Office XP

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Back
Top