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
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
Yes select E4:G4 and use a CF formula of

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

with a fill colour of blue.
 

lvalnegri

New member
Joined
Jan 31, 2012
Messages
11
Reaction score
0
Points
0
Location
London
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

New member
Joined
Oct 25, 2011
Messages
37
Reaction score
0
Points
0
Excel Version(s)
2016
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

New member
Joined
Jan 31, 2012
Messages
11
Reaction score
0
Points
0
Location
London
then the answer from Bob is all and only what you need. if you have XLS07+ you should also cancel the old CF.
 

thedeadzeds

New member
Joined
Oct 25, 2011
Messages
37
Reaction score
0
Points
0
Excel Version(s)
2016
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

New member
Joined
Jan 31, 2012
Messages
11
Reaction score
0
Points
0
Location
London
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

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
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

New member
Joined
Jan 31, 2012
Messages
11
Reaction score
0
Points
0
Location
London
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

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
You select C 4:E12 before applying the CF.
 

lvalnegri

New member
Joined
Jan 31, 2012
Messages
11
Reaction score
0
Points
0
Location
London
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.
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
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.
 
Top