Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Colour Rows
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Colour Rows
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 "Niniel" wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Colour Rows
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 -- Gary''s Student - gsnu200753 "Niniel" wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Colour Rows
Why? Why not use conditional formatting?
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Niniel" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Colour Rows
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... ----------------------------------------------------------------------- "Niniel" wrote in message ... 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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Colour Rows
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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Colour Rows
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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Colour Rows
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. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Colour Rows
Hi Niniel:
Note that using Cells(i,1) restricts the testing to column A because 1 means column A. -- Gary''s Student - gsnu200753 "Niniel" wrote: 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. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Colour Rows
Ah, ok, I didn't know that.
And yes, it does work as well. Thank you. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Colour rows | Excel Discussion (Misc queries) | |||
How do I use conditional formatting to colour rows of data? | Excel Worksheet Functions | |||
How do I save new colour schemes for graphs in the colour template | Charts and Charting in Excel | |||
Formula to change rows a different colour? | Excel Worksheet Functions | |||
make colour in rows stay | Excel Discussion (Misc queries) |