Solved: if cell="yes" then colour row yellow? how could do this?

[ThePigeon]

Hey

I know the title a bit confusing but I’ll try and explain what I’m trying to do.

I don’t think its not that complex...... but i can't quite work it out for myself.

I’ve got a data table and what i want to is make so when a cell in column F contains the text: "yes" all the cells in that row in the table are filled yellow.

Any help will be much appreciated

regards,
Joe

[mdmackillop]

Use Conditional Formatting. Select the required rows and enter the Formula =$F1="yes"

[stanleydgrom]

ThePigeon,

You could use Conditional Formatting.

Highlite the cells in the table, columns A thru E, and use:
=UPPER($F1)="YES"

[lucas]

vba solution:
Option Explicit
Public Sub HighlightRows()
Const TEST_COLUMN As String = "F" '<=== change to suit
Dim i As Long
Dim iLastRow As Long
Dim cell As Range
Application.ScreenUpdating = False
With ActiveSheet
iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = iLastRow To 1 Step -1
If .Cells(i, "F").Value = "yes" Then
.Rows(i).Interior.Color = vbYellow
End If
Next i
End With
Application.ScreenUpdating = True
End Sub

[ThePigeon]

wow did realise i could do it without vba.... cheers

did some research and read/watched a few guides and worked out a formula so the whole row is formated:

=INDIRECT("F"&ROW())="yes"

got a bit confused by what stanelydgrom and mdmackillop where saying buy i see now that it because i am using excel 2007 which has a whole new UI from conditional formatting.

cheers for the help,
regards,
Joe

[ThePigeon]

Cheers for the VBA option.... ill hang on to it.... might be useful in the future

regards,
Joe

[Bob Phillips]

Don't use INDIRECT, it is a horrible function, not needed here.

Just use a formula of

=F2="yes"

assuming 2 is the first row