Yes select E4:G4 and use a CF formula of
=OR($D4="full fail"),$D4="sense fail")
with a fill colour of blue.
Yes select E4:G4 and use a CF formula of
=OR($D4="full fail"),$D4="sense fail")
with a fill colour of blue.
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
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
then the answer from Bob is all and only what you need. if you have XLS07+ you should also cancel the old CF.
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
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.
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![]()
You select C 4:E12 before applying the CF.
Bookmarks