Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Here's the scenario: you have 2 columns, A and B. Column A sometimes has cells with the value "X" and Column B sometimes has cells with the value "Y". You want to highlight the entire row if either (or()) of those cells have that specific text in them - but only X for A, and only Y for B.. How can you do this?

    [codebox]
    A | B
    -----
    X |
    | Y
    X | Y
    |
    Y | X[/codebox]

    the first 3 rows in this instance should be highlighted. The fourth & fifth should be left alone.


    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The formula to use for the row would be:
    =or($a1="x",$b1="y")

  3. #3
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='mbarron' post='792808' date='10-Sep-2009 14:21']The formula to use for the row would be:
    =or($a1="x",$b1="y")[/quote]

    i know the or() formula.. but i thought that can only be used to highlight a single cell, no?
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  4. #4
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='officespacer' post='792811' date='10-Sep-2009 10:28']i know the or() formula.. but i thought that can only be used to highlight a single cell, no?[/quote]

    With the columns referenced absolutely, you can extend the formatting as far across the row as you want.


    I entered the formula in C1 and then copied it to the right and then down in the attached picture.

    I don't know if it's possible with your data, but is it possible for a valid and invalid entry? Such as an X in the A column and a Z in the B column.
    Attached Images Attached Images

  5. #5
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='mbarron' post='792816' date='10-Sep-2009 14:48']With the columns referenced absolutely, you can extend the formatting as far across the row as you want.


    I entered the formula in C1 and then copied it to the right and then down in the attached picture.

    I don't know if it's possible with your data, but is it possible for a valid and invalid entry? Such as an X in the A column and a Z in the B column.[/quote]

    Well, I can extend the formatting, but it doesn't highlight anything else- unless I'm doing it wrong. The other cells have other data irrelevant to the True/False column and when I select "format cells equal to" and select the cell that has "TRUE" in it, everything stays unformatted. It seems they will only format if they also have "TRUE" in them, which doesn't solve the problem.
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  6. #6
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You can highlight all cell you want to affect and then use the formula for the entire range.


    I've attached a sample workbook.
    Attached Files Attached Files

  7. #7
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='mbarron' post='792821' date='10-Sep-2009 15:15']You can highlight all cell you want to affect and then use the formula for the entire range.


    I've attached a sample workbook.[/quote]
    You are correct! Thanks
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

Posting Permissions

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