+ Reply to Thread
Results 1 to 5 of 5

conditional formatting based on column

  1. #1
    snax500
    Guest

    conditional formatting based on column

    In Excel2000, can I have a conditional format based on a formula
    reference. For example, I have data in columns A and C. In column B I
    have formulas that reference either column A or C ( +A15 or +C19). Can
    Excel use conditional formatting to look at the formula and then format
    it one way if in column A (red fill) and another in column C (yellow
    fill).

    Thanks


  2. #2
    zackb
    Guest

    Re: conditional formatting based on column

    Hi there,

    Think of Conditional Formatting much like that of a circular reference,
    because that is what it is. You are attempting to give it a True condition
    unto itself - and if everything lines up (is True) then the condition is met
    and the format is applied.

    I'll assume for a minute that we are talking about B15, where the formula in
    this cell is "=A15".

    Select B15
    Format (menu) | Conditional Formatting | Formula Is ..
    =B15=A15
    Format as desired

    So you see, it's less complicated to replicate the formula than it is to
    reference the actual formula statement; which is possible, but (imo) more of
    a pain than the alternative.

    Is this what you are asking for?


    --
    Regards,
    Zack Barresse, aka firefytr



    "snax500" <[email protected]> wrote in message
    news:[email protected]...
    > In Excel2000, can I have a conditional format based on a formula
    > reference. For example, I have data in columns A and C. In column B I
    > have formulas that reference either column A or C ( +A15 or +C19). Can
    > Excel use conditional formatting to look at the formula and then format
    > it one way if in column A (red fill) and another in column C (yellow
    > fill).
    >
    > Thanks
    >




  3. #3
    snax500
    Guest

    Re: conditional formatting based on column

    Thanks but your formula doesn't work if both column A and C are equal.
    Any other ideas.


  4. #4
    Bob Phillips
    Guest

    Re: conditional formatting based on column

    Add the first condition of

    =AND(A1=B1,B1=C1) and format accordingly, then extra conditions of
    =A1=B1, yellow
    and
    =B1=C1, red

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "snax500" <[email protected]> wrote in message
    news:[email protected]...
    > Thanks but your formula doesn't work if both column A and C are equal.
    > Any other ideas.
    >




  5. #5
    zackb
    Guest

    Re: conditional formatting based on column

    Well, you can do a couple of things. One would be to use an older technique
    that is a little tricky, but very doable. Follow these steps:

    Hit Ctrl + F3
    Name: TheFormula
    Refers to: =GET.CELL(6,INDIRECT("RC",FALSE))
    Hit Add
    Hit Ok

    Then select your cell in col B, goto Format (menu) | Conditional Formatting
    | Formula Is ...
    =TheFormula="="&ADDRESS(ROW(),COLUMN()-1,4)
    This will check the formula in the cell in column B, if it says "=A1" (or
    therow it is in) then the condition is true.

    Another method contains using a UDF ...

    Function MyFormula(Optional celRef As Range)
    If celRef Is Nothing Then Set celRef = Application.Caller
    MyFormula = celRef.Formula
    End Function

    Then use this conditional format for column B...
    =MyFormula()="=A"&ROW()


    --
    Regards,
    Zack Barresse, aka firefytr


    "snax500" <[email protected]> wrote in message
    news:[email protected]...
    > Thanks but your formula doesn't work if both column A and C are equal.
    > Any other ideas.
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1