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

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