Results 1 to 5 of 5

Thread: Formatting

  1. #1
    Join Date
    Jul 2004
    Posts
    2

    Question Unanswered: Formatting

    Hi, I have an spreadsheet with about 15 columns and 300 rows (and growing). I want to setup a conditional format that when 1 cell has a certain value, the entire row changes color. Any help would be appreciated! Thanks

  2. #2
    Join Date
    Feb 2002
    Posts
    8
    Hi JS..

    Highlight the entire row
    Format->Conditional Formatting...
    change the condition type to 'Formula Is'
    enter =($A$1="yes")
    (assuming that A1 is the conditional cell and "yes" is the conditional value)

    Dave.

  3. #3
    Join Date
    Jul 2004
    Posts
    2
    Do you know how to set that up so I don't go row by row, I want to enter just 1 condition that will apply to all rows.

  4. #4
    Join Date
    Feb 2002
    Posts
    8
    OK, try this:
    set the conditional format for all rows to:
    =(OFFSET($A$1,ROW()-1,1)="YES")
    This OFFSET function looks up a cell which is a specified number of columns and a specified number of rows away from cell A1.
    As it stands this formula will reference the cell in column B for each row. Changing the '1' that I have highlighted will change the column referenced (eg.
    =(OFFSET($A$1,ROW()-1,2)="YES") would reference column C
    =(OFFSET($A$1,ROW()-1,3)="YES") would reference column D
    etc...)

    Hope this makes sense,
    Dave.

  5. #5
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Slightly easier way is to leave the string out of the formula from before i.e.

    =(A$1="yes") to pick up columns or

    =($A1="yes") to pick up rows

    this works as long as your intial cell is A1

    HTH
    David

Posting Permissions

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