Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12

Thread: Conditional formatting

  1. #1

    Conditional formatting



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Yes select E4:G4 and use a CF formula of

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

    with a fill colour of blue.

  3. #3
    Seeker lvalnegri's Avatar
    Join Date
    Jan 2012
    Location
    London
    Posts
    11
    Articles
    0
    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

  4. #4
    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


  5. #5
    Seeker lvalnegri's Avatar
    Join Date
    Jan 2012
    Location
    London
    Posts
    11
    Articles
    0
    then the answer from Bob is all and only what you need. if you have XLS07+ you should also cancel the old CF.

  6. #6
    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

  7. #7
    Seeker lvalnegri's Avatar
    Join Date
    Jan 2012
    Location
    London
    Posts
    11
    Articles
    0
    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.

  8. #8
    Quote Originally Posted by lvalnegri View Post
    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.

  9. #9
    Seeker lvalnegri's Avatar
    Join Date
    Jan 2012
    Location
    London
    Posts
    11
    Articles
    0
    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

  10. #10
    You select C 4:E12 before applying the CF.

Page 1 of 2 1 2 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •