+ Reply to Thread
Results 1 to 3 of 3

Conditional Formatting of Table Rows based on value in a Column

  1. #1
    Registered User
    Join Date
    06-10-2012
    Location
    Bay Area, California
    MS-Off Ver
    Excel 2016
    Posts
    27

    Conditional Formatting of Table Rows based on value in a Column

    I'm working in Excel 2010.

    I setup a table with a column titled "Type". Each value under column "Type" defines the type of row it is: "Section", "SubSection", "SubSubSection"

    I wish to create a set of conditional formatting rules that apply to data rows in this table, each rule controlling how cells within a whole row should be formatted, according to the value found in "Type", for that row.

    Example:

    Rule1: [@Type]="Section" -> fat red line on top of cell.
    Rule2: [@Type]="SubSection" -> thin black line on top of cell.
    Rule3: [@Type]="SubSubSection" -> dashed black line on top of cell.

    I can't seem to make this work.

    How can I leverage the nice column names, and the "@"-this row designator, within a table to create conditional formatting rules that apply to all the cells within a row, in the same way one can refer to in table formulas?

  2. #2
    Forum Expert
    Join Date
    12-29-2004
    Location
    Michigan, USA
    MS-Off Ver
    2013
    Posts
    2,208

    Re: Conditional Formatting of Table Rows based on value in a Column

    I think I am following you. Try this:

    Select the range you want to format, including the Type column and all columns you wish to change the formatting. Assuming "Type" is column A and your data starts in row 2, you can create 3 conditions, using formulas. For the formulas, use:

    Rule 1:
    =$A2="Section" then specify formatting

    Rule 2:
    =$A2="SubSection" then specify formatting

    Rule 3:
    =$A2="SubSubSection" then specify formatting

  3. #3
    Registered User
    Join Date
    06-10-2012
    Location
    Bay Area, California
    MS-Off Ver
    Excel 2016
    Posts
    27

    Re: Conditional Formatting of Table Rows based on value in a Column

    Quote Originally Posted by jasoncw View Post
    I think I am following you. Try this:

    Select the range you want to format, including the Type column and all columns you wish to change the formatting. Assuming "Type" is column A and your data starts in row 2, you can create 3 conditions, using formulas. For the formulas, use:

    Rule 1:
    =$A2="Section" then specify formatting

    Rule 2:
    =$A2="SubSection" then specify formatting

    Rule 3:
    =$A2="SubSubSection" then specify formatting
    Yes, this works.

    I was lacking the initial "=" in the formulas for cond. formatting!
    Excel will choke if I try to use names of tables or columns to spec conditional formatting. As much as I'd prefer to spec these with column names to facilitate future maintenance, it's a minor gripe at this point. Excel will successfully manage reorganizing columns, as it'll rehash the rules to continue pointing to the desired column.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Conditional Formatting Rows based on Unique Values in Column
    By drjones5 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-13-2013, 04:27 PM
  2. [SOLVED] Conditional Formatting Entire Rows based on Change in Cell Value in Column A
    By Etain in forum Excel Formulas & Functions
    Replies: 26
    Last Post: 09-26-2012, 07:31 AM
  3. Replies: 6
    Last Post: 02-16-2012, 04:52 PM
  4. Creating a new Table based on Conditional Formatting in another table
    By JPKenny in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-06-2010, 11:48 PM
  5. Conditional Formatting Formula to Color Rows Based on Column Value
    By SteveC in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-26-2006, 01:25 PM

Tags for this Thread

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