• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

conditional formating

M

momi

New Member
Hi, could somebody help me? I have a two columns with numbers and I need to condition format my first column based on value in second column, in rows a different values, I am able to set conditional formation for each row separately, but I have too many rows ... thanks for help. Michal
 
When you setup your CF rule, make sure the row reference is not absolute (doesn't use a dollar sign) e.g.:

=$B2>5


That way when you copy the formatting to another cell in a different row, the rule will automatically adjust.
 
Set the conditional format for one cell, then copy it. Select the rest of the cells in the column and right-click, choose "paste+special", then choose "formats".
 
yes, I did it before (deleting of dolar), but message "you cannot use relative references in conditional formating crireria for color scales, data bars and icon sets" appeared.
 
even when I copy paste special, second line compared value with first row :-( What I am doing wrong? thanks
 
conditional formatting can be touchy. Can you provide the exact format you're trying to use, and what cell you're putting the format in?
 
Putting the conditional format expression and cell reference on the forum will likely get you an optimum response.
 
OK, in first column (C_column) there is a formula, result is in %, in a second column (D_column) there is also formula with result in %, then in Conditional Formatting Rules Manager: applies to: =$C$37 (right now I am in a row 37), In "Edit Formatting Rule": type: Format all cells based on their values", then I selected icons, and when value is >= =$D$37 type: formula(I set icon OK), when < Formula and >=0, Type: percent (I set icon NOT OK). This works for row 37, but I need to copy it to next 100 rows. thanks a lot. Michal
 
Your AND expression should look like this:


=AND($D37*3>=$C37,$D37>0)


The other format should be something like:


Cell Value > $D37


Note that the "$" were removed per LukeM's comment earlier.


Assuming the conditional formatting works in C37 and D37, highlight these two cells and hit Ctrl-C. Click and drag over the new cells you want to format. In the Home ribbon, click on the paste drop-down and select "Paste Special" > "formats". Click "OK". (This may change depending on what Excel version you're using, but it'll be really close.)
 
You must log in or register to reply here.
Back
Top