Conditional Formatting adjacent cells

[Rachel Curran]

Hi,

I'm trying to conditional format adjacent cells on my worksheet, For
example please see table below:

Emp No Name DOB Dept Grade Decision
12345 Rachel 28/02/78 HR A Yes
45678 Ian 15/10/71 Head Office B Yes
55678 James 15/11/81 Finance C No
55678 John 11/09/78 Finance A Yes
48567 Matt 20/09/56 HR B No

I somehow need to check what is present in the decision column (F) and
depending on whether the result is Yes or No colour format the rest of
the row of data. For example I want all Yes to be red and all No's to be
green. When i attempt to format in code the cell with the Yes/No
in it is the only cell that gets formatted - how can i format the
whole row depending on the result in column F (decision).

Also,

How do i select one row after another and how can you tell the
computer when it has reached a row with no text present.

Any help would be greatly appreciated.

R Curran

 

[Rog]

Do you want to do this with code? Because you could just
do this using the Format|Conditional Formatting menu option

Rgds

Rog

 

[David McRitchie]

Hi Rachel,
You do the conditional formatting to the entire row based on a specific
column the same as you do when you test a column. Just make sure that your
column has absolute reference for the column portions.

The cells that get colored are based on the selection, the formula that you
use is based on the active cell. I would only use formulas myself and not
the "simplified" comparisons. More information on Conditional Formatting
with examples in http://www.mvps.org/dmcritchie/excel/condfmt.htm

Select cell F1
Ctrl+A to select all cell on the worksheet, F1 remains the active cell
Format, Conditional Formating
Formula 1: =F1="Yes" format with a pastel blue pattern
I think that would be enough color for Yes/No or blank, but if you want
Formula 2: =F1="No" format with a pastel yellow pattern

Don't worry about the empty cells, if you didn't include them in your
test you won't be adding color. Conditional formatting has to get
a True condition or a False condition. Errors such as text in a
numeric test are treated as False so you don't need to include a lot
of additional tests as when you are trying to calculate a value.

If you wanted to show empty cells as an incomplete condition
C.F. formula 3 is: =TRIM(D3}=""
or
C.F. formula 3 is: =AND(TRIM(D3)="", A3<>="")

------
As another example your data could use Conditional Formatting like this:
Select cell D1
Ctrl+A to select all cell on the worksheet, F1 remains the active cell
Format, Conditional Formatting
Formula 1: =D1="HR"

There is a limit of 3 formulas per cell, for C.F. since you are involving the
entire row, that means that every cell in the selected rows is included in
the test, in other words each cell in the rows is tested. Since you are
limited to 3, you might want to combine some.

Formula 1: =OR(D1="HR", D1="Head Office")
Formula 2: =D1="Sales"
Formula 3: =D1="Facilities"

If you need more than three you would have to use an Event Macro
http://www.mvps.org/dmcritchie/excel/event.htm#case

You might also consider Data, Filter, .... to show only the
rows you are interested in. Debra Dalgleish has pages on
Filtering. Much of what you use in Conditional Formatting applies
to Filtering as well. The drop down list of unique items was increased
in Excel 97 and Excel 98 to 1000 from 250. [MSKB 105322].
Excel Filters -- Advanced Filter, Debra Dalgleish.
http://www.contextures.com/xladvfilter01.html#ExtractWs
Additional links on my sumdata.htm#related


HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Do you want to do this with code? Because you could just
do this using the Format|Conditional Formatting menu option

Emp No Name DOB Dept Grade Decision
12345 Rachel 28/02/78 HR A Yes
45678 Ian 15/10/71 Head Office B Yes
55678 James 15/11/81 Finance C No
55678 John 11/09/78 Finance A Yes
48567 Matt 20/09/56 HR B No

 

[Rachel Curran]

Do you want to do this with code? Because you could just
do this using the Format|Conditional Formatting menu option

Rgds

Rog

Yes, I need this to be in code please

 

[David McRitchie]

Hi Rachel,
See my previous response in this thread, but it sounds like you
are still looking for an answer; otherwise you would have said you
had your answer.

This example will change the color of the entire row based on
the value manually entered into Column F (column 6) having
"Yes" or "No" with an Event Macro instead of using Conditional Formatting. .

VBA is case sensitive whereas the worksheet formulas used in
Conditional Formatting were not, so the testing will be of the
value as converted to lowercase.

Read again about Event Macros in
http://www.mvps.org/dmcritchie/excel/event.htm#case

To install right click on the sheet tab then View code, insert code

Private Sub Worksheet_Change(ByVal Target As Range)
'David McRitchie, 2004-09-26, programming, Case -- Entire Row
' http://www.mvps.org/dmcritchie/excel/event.htm#case_row
If Target.Column <> 6 Then Exit Sub 'Column F is column 6
If Target.Row = 1 Then Exit Sub
Application.EnableEvents = False 'should be part of Change macro
Select Case LCase(Target.Value)
Case "yes"
Target.EntireRow.Interior.ColorIndex = 34
Case "no"
Target.EntireRow.Interior.ColorIndex = 36
Case Else
Target.EntireRow.Interior.ColorIndex = xlColorIndexAutomatic
End Select
Application.EnableEvents = True 'should be part of Change macro
End Sub