Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Shading cells with a Macro
I need help with this...please! Easiest way to explain this is...I have ~40,000 lines of data that I need to look at. To make the data more readable for review, I want to separate account records with shading (i.e. records for account 1 are not shaded, records for account 2 are shaded, records for account 3 are not shaded,............etc). I tried using following macro, but one, it did not work and two, I will not know in advance what the account numbers will be in advance (the data changes week to week) Sub colorcode() For nrow = 2 To 40000 If Cells(nrow, 2) = "G52-555222" Then Range(Cells(nrow, 1), Cells(nrow, 7)).Interior.ColorIndex = 8 ElseIf Cells(nrow, 2) = "W5H-222999" Then Range(Cells(nrow, 1), Cells(nrow, 7)).Interior.Color = RGB(255, 255, 192) ElseIf Cells(nrow, 2) = "M52-999222" Then Range(Cells(nrow, 1), Cells(nrow, 7)).Interior.Color = RGB(204, 255, 204) Else Range(Cells(nrow, 1), Cells(nrow, 7)).Interior.Color = RGB(255, 255, 255) End If Next Range("a1").Select End Sub Please see attached excel file. Sheet 1 is raw data. Sheet 2 is the desired result. I am thinking I can create a macro or use some type of conditional formatting to create the desired result. Thanks in advance for any comments. +-------------------------------------------------------------------+ |Filename: shading_example.ZIP | |Download: http://www.excelforum.com/attachment.php?postid=3702 | +-------------------------------------------------------------------+ -- maacmaac ------------------------------------------------------------------------ maacmaac's Profile: http://www.excelforum.com/member.php...fo&userid=2959 View this thread: http://www.excelforum.com/showthread...hreadid=396825 |
#2
|
|||
|
|||
Have a look at Format|Conditional Formatting
best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "maacmaac" wrote in message ... I need help with this...please! Easiest way to explain this is...I have ~40,000 lines of data that I need to look at. To make the data more readable for review, I want to separate account records with shading (i.e. records for account 1 are not shaded, records for account 2 are shaded, records for account 3 are not shaded,............etc). I tried using following macro, but one, it did not work and two, I will not know in advance what the account numbers will be in advance (the data changes week to week) Sub colorcode() For nrow = 2 To 40000 If Cells(nrow, 2) = "G52-555222" Then Range(Cells(nrow, 1), Cells(nrow, 7)).Interior.ColorIndex = 8 ElseIf Cells(nrow, 2) = "W5H-222999" Then Range(Cells(nrow, 1), Cells(nrow, 7)).Interior.Color = RGB(255, 255, 192) ElseIf Cells(nrow, 2) = "M52-999222" Then Range(Cells(nrow, 1), Cells(nrow, 7)).Interior.Color = RGB(204, 255, 204) Else Range(Cells(nrow, 1), Cells(nrow, 7)).Interior.Color = RGB(255, 255, 255) End If Next Range("a1").Select End Sub Please see attached excel file. Sheet 1 is raw data. Sheet 2 is the desired result. I am thinking I can create a macro or use some type of conditional formatting to create the desired result. Thanks in advance for any comments. +-------------------------------------------------------------------+ |Filename: shading_example.ZIP | |Download: http://www.excelforum.com/attachment.php?postid=3702 | +-------------------------------------------------------------------+ -- maacmaac ------------------------------------------------------------------------ maacmaac's Profile: http://www.excelforum.com/member.php...fo&userid=2959 View this thread: http://www.excelforum.com/showthread...hreadid=396825 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is there a formula, function or macro that accounts for shading of a cell or row? | Excel Discussion (Misc queries) | |||
Correctly copy cells with a macro | Excel Discussion (Misc queries) | |||
When shading cells using the 'pattern' option, they print in grey. | Excel Discussion (Misc queries) | |||
Blink a cell's shading | Excel Discussion (Misc queries) | |||
Macro to hide rows with empty cells | Excel Worksheet Functions |