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
|