• 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.

Multiple Conditional formatting

J

jackmanjls

Member
Is it possible to have a single rule that controls more than one cell color when doing a conditional formatting rule.


For example, assume that we have a single cell that we want to color either red or green. Can we say something like if cell > 0 color green, if cell < 0 color red. I know how to do this using 2 rules but can combination be done?


Is this possible?
 
Jackmanjls


Well sort of


You can set a default color for the cell

Then apply a CF

So in your case set a default color of Green

Then set a CF of If Cell<0 Red


It can't be extended past 2 colors without additional CF rules
 
Hui,

You mentioned "It can't be extended past 2 colors without additional CF rules". To me this implies that it can be done in a single rule but the logic would be more complicated, is this correct?


If it can be done in a single rule can you give me some keywords that I can google to get started? The site youtube.com probably has a vid.


Could this be done with an "if" statement? If so, how does one manipulate the colors in CF?
 
You haven't suggested that you need more than two colours, so isn't it irrelevant? If you had more condition such as =0, then you would just add a rule to the CF to accommodate that extra condition and format (colour).
 
Jackmanjls


I need to be careful saying Can't, Every time I've said that somebody pops up a solution.


I am unaware of any way of doing multiple Colors in a CF using only 1 CF Rule/Formula


Of course using the CF Data Bars, Color Scales and Icons give you some flexibility


As XLD implied, What is your requirement?
 
To be more precise.


Each stock trading day I enter in 2 numbers into a spreadsheet(ss)the advanced (adv) and declined (dec) stocks for the day. These numbers are used to generate 2 additional columns. The total # cols for each day is 4, they are: adv, dec, breadth1 and breadth2. Based on there values cols can have up to 4 diff colors. Each trading day has a single row with 4 cols.


So, depending on the relationship of the adv, dec and breadth1+2 the cells can be filled with several diff colors. The logic for the color schemes has been successfully tested in a test ss using CF single rules.


Here's a problem that I'm having. As I enter the data for each trading day I add a row at the top of my ss by placing the cursor over the existing to row and press Alt+I+R. This adds a blank row. What I would like to do is have the new row have all the attributes of the previous ss rows so that I don't have to go back and manually enter/copy the CF rules and any formulas.


Any ideas?
 
So you clearly understand the way to have multiple conditions with separate formats.


Do you have Excel 2007 or 2010? IF so, you could format your data as a table, when you add a new row the attributes of the table row are inherited.
 
I have XL2007.

I have created my ss using CondFormat (CF). All the colors and logic are correct and the way that I want them.


I add a single row at the top of the ss every stock trading day.


Here's the issue. When I add a new row I manually enter 4 numbers. The previous cells for these manual numbers have CF coloring properties.


Q1: how do I copy the CF properties from the previous cell to the new cell to get the desired coloring. Make note that I'm only talking about those cells where I do the manual entry, the other cells I do a simple copy.


Q2: a more broader question. I would like to automate the entry of the row and have the correct CF properties. So, is there anyway to enter a row where I can automatically pick up the attributes of the previous row? Would a macro do this?
 
Jackmanjls


If you insert an entire Row the CF's get copied down from the Row above

If you insert some cells the Cf's don't get copied down


I would leave a blank Row with the CFs applied at the top of your data and then insert a Row below that, Is that possible ?
 
The 1st row of the ss is the column headings, they are in bold. The 2nd and successive lines have the data with CF properties.


If i highlight the 1st line of data (not the heading row but the 1st data row) and add a line using Alt-I-R it doesn't copy the CF properties, in fact, the line has the bold properties, like the heading row. However, if i scroll down to the next line of data and add a line it copies all the CF attributes.


Is there something that I'm not doing correctly?
 
Hi, jackmanjls!

Give a look at the second green sticky post at this forums main page for uploading guidelines.

Regards!
 
This is the link: http://speedy.sh/v6jaB/SampleChandoo.xlsx


The quest is to add a new row that has all the CF properties as the other data rows.
 
Jackmanjls


As per my previous post above:

I inserted a Row between 2 & 3 and it takes the CF's with it

Then Copy Row 2 to the new Row 3

Delete all the values in Row 2, the cells still have the CF's maintained

Now Hide Row 2

Now when you select Row 3 and Insert a new Row it will copy the CF's down into the new Row

You will still have to copy the formulas up from Row 3
 
In conclusion I'd like to document what I did.

I copied row 2 with the CF properties and then did a hide.

I then wrote a small macro that inserted a row below the hidden row using Alt+I+R. In the new row, 1st col I added today's date (Ctrl+;) and then copied the CF properties to the new row. I then placed the cursor on the cell of the 1st data entry and ended the macro.


I now have a quick way to add my daily stock data.


Thanks Hui, SirJB7, xld for your help. Who ever answers this please let the others know that I appreciate there assistance.
 
You must log in or register to reply here.
Back
Top