MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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


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

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

Posted by Eric on October 11, 2001 8:16 AM

Also, if you want to do multiple rows

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

Posted by Eric on October 11, 2001 8:34 AM

Was there some issue with this conditional row formatting procedure?

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?

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

Re: Also, if you want to do multiple rows

> 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

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

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

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

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

Re: Also, if you want to do multiple rows

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