Colour Rows

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
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
 
Why? Why not use conditional formatting?


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
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...
 
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.
 
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.
 
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.
 
Hi Niniel:

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