Shading cells with a Macro

[maacmaac]

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.

[Bernard Liengme]

Have a look at Format|Conditional Formatting
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"maacmaac" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>