Archive of Mr Excel Message Board


Back to Excel VBA archive index
Back to archive home

Conditional Formatting

Posted by Richard on October 11, 2001 6:44 AM
how do i format a whole row to stand out dependent on one cells contents.

thanks


Check out our Excel VBA Resources

Re: Conditional Formatting

Posted by Jonathan on October 11, 2001 7:30 AM
Here's an example. This will only select one row. So if two rows or more fit the bill, only the last one will still be selected at the end of the code. There may be a way to emulate Ctrl + click but I don't know it offhand.
Also you may want to adjust the loop to be more generic.

(Watch out for linewrap.)

Sub StandOut()

Dim i As Integer

For i = 1 To 50
If ActiveSheet.Cells(i, 3).Value = "Jonathan" Then
ActiveSheet.Cells(i, 3).EntireRow.Select
End If
Next i

End Sub


Re: Conditional Formatting

Posted by Aladin Akyurek on October 11, 2001 7:58 AM
> how do i format a whole row to stand out dependent on one cells contents.

Lets say that you want to color row 3 as a whole based on a value in A1: If A1=6, row 3 turns lavender, otherwise it stays as it is.


Click on row 3 (on the row indicator).
Activate Format|Conditional Formatting.
Select "Formula Is" for Condition 1 and enter

=$A$1=6

Activate the Format button.
Select your color on the Patterns tab.
Click OK.
Click OK.

Aladin


Also, if you want to do multiple rows

Posted by Eric on October 11, 2001 8:16 AM
Following along with Aladin's suggestion, if you remove the row constraint from the formula (from $A$1 to $A1, for example), you can copy row 1, and Edit|Paste Special|Formats to all the other rows you want conditionally formatted. Aladin


Was there some issue with this conditional row formatting procedure?

Posted by Eric on October 11, 2001 8:34 AM
I dimly recall a thread in which Aladin and Mark W and others(should have been in last archive but I can't find it)discussed some sort of bug/problem with this conditional row formatting procedure. Am I misremembering?


Re: Also, if you want to do multiple rows

Posted by Aladin Akyurek on October 11, 2001 8:44 AM
> Following along with Aladin's suggestion, if you remove the row constraint from the formula (from $A$1 to $A1, for example),

REMOVING THE CONTRAINT: THAT'S PRECISELY THE POINT IF YOU WANT TO COVER A BIGGER AREA THAN A SINGLE ROW.

> you can copy row 1, and Edit|Paste Special|Formats to all the other rows you want conditionally formatted. : > how do i format a whole row to stand out dependent on one cells contents. : Lets say that you want to color row 3 as a whole based on a value in A1: If A1=6, row 3 turns lavender, otherwise it stays as it is. : : Aladin


Re: Was there some issue with this conditional row formatting procedure?

Posted by Aladin Akyurek on October 11, 2001 8:48 AM

No, you're not misremembering. I searched very fast to find a trace of it on my hard disk. Did not succed, alas. As you rightly pointed in your other post, it's a question of which constraints must be effected in the cell refs that are required. : > how do i format a whole row to stand out dependent on one cells contents. : Lets say that you want to color row 3 as a whole based on a value in A1: If A1=6, row 3 turns lavender, otherwise it stays as it is. : : Aladin


Re: Also, if you want to do multiple rows

Posted by Richard on October 11, 2001 11:58 PM
Many thanks,

that worked a treat - the condition was based on a text, i.e. Y or N - so i just had to add quotations round the condition

Richard


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.