Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Fitz
 
Posts: n/a
Default Auto color-shading of rows

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.


  #2   Report Post  
Bob Phillips
 
Posts: n/a
Default Auto color-shading of rows

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)


"Fitz" wrote in message
.. .
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.




  #3   Report Post  
Fitz
 
Posts: n/a
Default Auto color-shading of rows

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


  #4   Report Post  
Bob Phillips
 
Posts: n/a
Default Auto color-shading of rows

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)


"Fitz" wrote in message
.. .
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




  #5   Report Post  
Fitz
 
Posts: n/a
Default Auto color-shading of rows

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




  #6   Report Post  
Bob Phillips
 
Posts: n/a
Default Auto color-shading of rows


"Fitz" wrote in message
. ..
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


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.


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



  #7   Report Post  
Tushar Mehta
 
Posts: n/a
Default Auto color-shading of rows

In article , fitz1
@sympatico.ca says...
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.



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
  #8   Report Post  
Posted to microsoft.public.excel.charting,microsoft.public.excel.misc,microsoft.public.excel.worksheet.functions
Adrian
 
Posts: n/a
Default Auto color-shading of rows

Try using conditional formatting under the format menu

"Fitz" wrote:

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.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to have formulas and formats auto copy to new inserted rows DippyDawg Excel Discussion (Misc queries) 2 August 5th 05 03:09 PM
auto deleting defined rows LoriM Excel Discussion (Misc queries) 4 August 4th 05 06:20 PM
Column Auto Width and Hidden Rows KWCounter Excel Discussion (Misc queries) 1 May 20th 05 12:59 AM
Auto adjusting # rows between 2 worksheets kevin mckellar New Users to Excel 1 December 30th 04 03:01 PM
AUTO HIDE ROWS Alan Excel Worksheet Functions 1 November 27th 04 09:13 AM


All times are GMT +1. The time now is 11:40 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"