Conditional Formatting

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

> 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

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?

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

> 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?

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

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