Colour Rows

N

Niniel

Hello,

I need a macro that will colour all rows in, say, neon blue, for all cells
in column A that contain the letter "P".

How could that be done?

Thank you.
 
J

Joel

Sub colorblue()

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For RowCount = 1 To LastRow
If Range("A" & RowCount) <> "" Then
If InStr(Range("A" & RowCount).Value, "p") Then
Range("A" & RowCount).Interior.ColorIndex = 8
End If
End If
Next RowCount
End Sub
 
G

Gary''s Student

try this:

Sub neon()
n = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To n
v = Cells(i, 1).Value
If Len(v) <> Len(Replace(v, "P", "")) Then
Cells(i, 1).EntireRow.Interior.ColorIndex = 5
End If
Next
End Sub
 
B

Bob Phillips

Why? Why not use conditional formatting?


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
E

Earl Kiosterud

Niniel,

Use Conditional Formatting. Select the rows. Note the active (white) cell of your
selection. Format - Conditional Formatting - Formula is:

=$A2 = "p"

This is for the case where the active cell of your selection was in row 2. Change the
reference to $A2 as appropriate. Click "Format" and select the Patterns tab, and the blue
color you want. OK. OK.

This is case insensitive. It will work for "P" and "p".
--
Regards from Virginia Beach,

Earl Kiosterud
www.smokeylake.com

Note: Top-posting has been the norm here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...
 
N

Niniel

Thank you, Joel, that works in that it colours the cells in column A. How can
I get the entire row to be coloured?

Btw, is there a way to exclude a specific row? Or colour it differently?
My row 2 is a header row, and I don't really need colour it. Light grey
might be nice, but it's not that important.
 
N

Niniel

Thank you, Gary.

I didn't see your how your code was only checking column A, so I went with
Joel's. But I did update his code with your "EntireRow.Interior.ColorIndex".
:)

So that works great now.
Giving row 2 special treatment would be even better (see my previous post),
but I could live with this as is.
 
N

Niniel

Bob, Earl,

I will have to use this a lot on different sheets, so I want to have a
button I can press without having to do anything else, and for that I need a
macro, I think.
This works very nicely now.

Thanks for taking a look, I appreciate it.
 
G

Gary''s Student

Hi Niniel:

Note that using Cells(i,1) restricts the testing to column A because 1 means
column A.
 
Top