PDA

View Full Version : Conditional formatting



thedeadzeds
2012-02-01, 07:00 PM
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
2012-02-01, 08:14 PM
Yes select E4:G4 and use a CF formula of

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

with a fill colour of blue.

lvalnegri
2012-02-01, 08:37 PM
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
2012-02-01, 09:06 PM
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
2012-02-01, 09:23 PM
then the answer from Bob is all and only what you need. if you have XLS07+ you should also cancel the old CF.

thedeadzeds
2012-02-01, 09:31 PM
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
2012-02-02, 12:30 AM
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
2012-02-02, 10:59 AM
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
2012-02-02, 05:47 PM
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
2012-02-02, 07:56 PM
You select C 4:E12 before applying the CF.

lvalnegri
2012-02-02, 08:33 PM
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
2012-02-03, 01:38 AM
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.