Conditional formatting

[thedeadzeds]

Guys,

is this possible with a formula in condtional formatting?

If cell d4 is = to "full fail" or "sense fail"

then

cells e4, f4 and g4 all turn blue

and so on along the workbook

many thanks
craig

[Bob Phillips]

Yes select E4:G4 and use a CF formula of

=OR($D4="full fail"),$D4="sense fail")

with a fill colour of blue.

[lvalnegri]

if you must put a lot of them, insert this in a module

Sub whatFail()
Dim MyRange As Range
Set MyRange = Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(3, 0))
MyRange.FormatConditions.Add Type:=xlExpression, Formula1:="=OR(" & Selection.Address & "=""full fail""," & Selection.Address & "=""sense fail"")"
MyRange.FormatConditions(1).Interior.Color = -4165632
Set MyRange = Nothing
End Sub

and run it from the upper cell (D4). you could also enclose it in a loop to CF the entire worksheet

[thedeadzeds]

thanks guys but not really what I looking for. I have this formula =AND($C3>0,LEN($F3)=0). This changes F3 to a colour if C3 has data in it. I bascially want to amend it to do the following:

If cell d4 is equal to "full fail" or "sense fail"

then

cells e4, f4 and g4 all turn blue

[lvalnegri]

then the answer from Bob is all and only what you need. if you have XLS07+ you should also cancel the old CF.

[thedeadzeds]

thanks lvalnegri but the formula from Bob does not work, it brings up an error showing 'the formula you typed contains an error. Also, I would want to then apply this to the next cells and so on, so I dont think this will work anyway i.e

If cell d4 is = to "full fail" or "sense fail"

then

cells e4, f4 and g4 all turn blue

and

If cell e4 is = to "full fail" or "sense fail"

then

cells f4, g4 and h4 all turn blue

and

If cell f4 is = to "full fail" or "sense fail"

then

cells g4, h4 and i4 all turn blue

and so on

[lvalnegri]

oh yes, there's an extra parenthesis. the right one is

=OR($D4="full fail",$D4="sense fail")

but you should do it manually and separetely for every single group of cells.
that's why I proposed the vba code. put it in a loop and it CF an entire area in a single step.

[Bob Phillips]

but you should do it manually and separetely for every single group of cells.
that's why I proposed the vba code. put it in a loop and it CF an entire area in a single step.

Why?
You can apply CF to a selection of many cells in one go from Excel.

[lvalnegri]

yes, I know, but I can't figure out how you can do it in this case. how do you proceed if you want to do it in one go for the range C4:E12?
BTW, I've just realized that my code is in vertical mode

[Bob Phillips]

You select C 4:E12 before applying the CF.