Conditional formatting

thedeadzeds

New member
Joined
Oct 25, 2011
Messages
37
Reaction score
0
Points
0
Excel Version(s)
2016
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
 
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.
 
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.
 
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.
 
sorry, I had to ask for a bigger range like C4:H12. If you apply your formula, in this case =OR($B4="full fail",$B4="sense fail") because of the absolute reference for the column, you can fulfil the condition only in column B (and not in column C for the range D:F and so on) and when the condition in B is satisfied the results are applied at all the columns from C to H, and not only at C:E.
 
In the case the guy was asking, he wanted to colour E:G whenever B met the condition, so he would select En:Gm and apply the formula I have (using the n value)> The B was absolute column so that each of E:G would check against the same column, B, it wouldn't shift dynamically.

I really do not understand what point you are trying to make.
 
Back
Top