Question for bigwheel

[Paul Sheppard]

Hi bigwheel

I am not very good at VBA and do not understand the reply you gav
below:

Try something like this. You can pick your own colors by changing the
ColorIndex or RGB values:-

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

What in the code makes it change at every change in column 1? How woul
this be adapted for changes in other columns? When it pastes in th
colours it removes the gridlines can it be donne without removing th
gridlines? What do "G52-555222", "W5H-222999", "M52-999222" mean/refe
to? What is nrow?

Sorry if I seen vague, but I find this a good way to learn

Thanks in advance Pau

 

[Guest]

This particular reply was tailored in response to a posting on the excelforum
by mcnenny. In the example worksheet posted under the name “Shading
problemâ€, column 2 was headed “Account #â€

Basically, what the macro does is starts at row 2 (row 1 assumed to be
column headings) and checks for an account # in column 2 (Cells(nrow,2) where
nrow is the counter in the For … Next loop.) If the value is G52-555222, the
cells A2 to A7 are coloured, in this case, cyan. Otherwise, if the account
W5H-222999 is found, the row is coloured pale yellow etc. To adapt this for
other columns, change the 2 to 3 or 4 or whatever. The loop continues at the
Next statement and nrow is incremented to 3 and the whole thing starts over
but on row 3 until it reaches the end (in this case row 40000)

Blocks of colour will hide the gridlines but you can overcome this by
setting borders on all the cells