Conditional Format.........Help!
Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Conditional Format.........Help!

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    41
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Is there a formula or a conditional format that will turn an entire range of cells a different color, when the value of one cell is above or below a specific range?

  2. #2
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Davin,

    Use Conditional Formatting > Formula Is:

    1. Formatting a cell if the value is above some value or below some other value:
    =OR($B$1>$A$1,$B$1<$C$1)

    2. Formatting if the row is greater than the row corresponding to the value of another cell (i.e A1 has value of 15, highlight all rows greater than 15)

    =ROW()>ROW(INDIRECT(ADDRESS(MAX(1,A1),COLUMN(),4,1)))

    Format to suit. Modify the cell references as needed.

    Bye,
    Jay

  3. #3
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Jay:
    Can you elaborate on the conditional format formula you gave in case 2) in response to a post from Davin ... THANKS!

  4. #4
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Yogi,

    Suppose I have the following:

    Test Cell: F16
    Value cell housing the criteria: A1 (suppose it is currently 10)

    This means that is there is a 25 in cell A1, I want the Conditional Formatting to kick in for cells (rows, etc.) in rows 26 or above. In this case, I do not want the cell to be changed. When 10 is in cell A1, then F16 is highlighted as it evaluates to true in rows 11 and above.

    So, I am in F16 and Format>Conditional Format>Formula Is...

    =ROW()> in this case is 16 greater than...

    ROW(INDIRECT(ADDRESS(MAX(1,A1),COLUMN(),4,1)))

    Working inside out...

    1. ADDRESS(MAX(1,A1),COLUMN(),4,1)

    Standard ADDRESS function call, but the reference to cell A1 cannot be zero, as I am using this result in a call to ROW(...) and it would throw an error if it had zero in the A1 (ROW 0).

    The column() isn't really used, but is required for the address function. You can specify a column if you wish.

    With 10 in A1, and F16 my target, active cell, this returns F10, which is text.

    2. INDIRECT(....)
    Evaluates the address as if it were a call to cell F10, so instead of reading text, Excel is looking at cell F10.

    3. ROW(INDIRECT(...))
    Essentially performs the same as if you wrote =ROW(F10)

    Putting it all together, we have

    =16>10 = TRUE and conditional formatting kicks in.

    This can be improved, as I didn't test if there were text in A1, and I've read that using INDIRECT and ADDRESS together is inefficient.

    Regards,
    Jay

  5. #5
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Thanks Jay:
    I hear you alright ... but I will acytually have to key a couple of things in to properly digest it ... T H A N K S !

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com