Results 1 to 5 of 5
  1. #1
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Conditioning Format with formula in it (XP)

    I have a range of Cells B1:O5000.

    Each cell has a MID formula in it similar to =MID($B1,1,1) , =MID($B1,2,1) ,...,=MID($B1,13,1)

    This continues down the length of the columns

    I am trying to determine which cells contain the value 1 and have been trying to use Conditional Formatting to identify these cells and change the back color. This is not working. Is it because I have formulas in the cells, or am I missing something?
    Jerry

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditioning Format with formula in it (XP)

    Assuming that the formulas are in C1:O5000:
    - Select C1:O5000.
    - Select Format | Conditional Formatting...
    - Select Formula Is from the first dropdown box.
    - Enter the formula =(C1="1") in the box next to it.
    - Click Format... and set the fill color.
    - Click OK (2x)

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditioning Format with formula in it (XP)

    Or, even simpler, select Value Is, Equal To, and enter ="1" in the box for the value.

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Conditioning Format with formula in it (XP)

    Gggrrrrr, I missed out the inverted commas

    Thanks beautiful listed of binary numbers all coloured in now <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Jerry

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditioning Format with formula in it (XP)

    The reason for the quotes is that MID returns a string value. If C1 contains 2134, MID(C1,2,1) is the string "1", not the number 1.

Posting Permissions

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