Conditional Formatting

Topic

New Reply

Attached is a dummied-up page from one of my Excel 2010 workbooks. (The actual page is full of formulas that refer to other sheets in the workbook, which is too big to upload here.) I want to change some of its Conditional Formatting settings, but it’s a bit complex and I’m not sure of the best way to handle it.

Presently, if the amount in a cell in Column G exceeds $95,000,000, the background of that cell turns green.

I want to change it so that the background of the cell stays as it is, but the font in the cell turns green and becomes bold.

The light yellow cells all have “MM” in Column C. Their yellow fill color is 255,255,204. The light orange cells all have “PB” in Column C. Their orange fill color is 252,213,180.

In specifying Conditional Formatting, I don’t see that it is possible to leave the fill color as it is, while changing only the font color. That would be the easy way, if it can be done.

Another way might be to incorporate some sort of IF statement in the Conditional Formatting rule, and apply it to $G$6:$G61. The IF would format the cell’s fill color depending on the value in Column C. I’m not sure how to create the IF statement, or if it can even be done that way.

A third way would be to format each cell in Column G, making the fill color whatever it is right now. I don’t mind manually formatting all those cells, but I’m thinking it might be a nightmare to audit everything to make sure it works.

All help or suggestions will be gratefully received.

Reply | Quote

Replies

Hi Lou

Aha! We like complex!
..but it’s not really that tough when you examine it..
The attached file has what you asked for.

I used 2 formatting rules, to get the two background cell colours for odd/even numbered rows.

I noticed that your sample file rows 57 to 61 had ‘grey’ background.
This seems to be determined by whether corresponding column had the Lottery Drawing numbered entered or not.
So, my conditional formatting formulas were like this:
=AND(S6>0,G6>95000000,MOD(ROW(G6),2)=1)

=AND(S6>0,G6>95000000,MOD(ROW(G6),2)=0)

zeddy

Reply | Quote

Looks good, but I haven’t gotten deeply into it yet.

I DO like complex :-). Basically, I know what I want, and I’m willing to go a long way to get it. This workbook has to do with a quarterly lottery pool that I’ve been running for almost ten years. The workbook is very highly developed, thanks in no small part to help I’ve gotten from this Lounge. When there are found to be some loose ends, they are usually pretty small ones, but I try to address them nevertheless.

There are four lottery drawings each week, and thanks to Excel, it takes me less than five minutes to see if and what we have won, to post it to Twitter and our website, and to post the amount of the next jackpot. It is REALLY slick and efficient. This little fix is to make it easier to put the green items into the table on the website. You can see it all at http://www.ussrankin.org/lottery/

By the way, I re-use the workbook every three months. (I copy it and clear all the data from the copy). When there’s not a drawing on a particular day, I just gray it out by hand. That makes it easy to reuse the sheet in question here, since the drawings are all on the same days of the week each quarter.

Reply | Quote

Zeddy,

FYI: the =1 & =0 in your formulas are not necessary since in Excel zero = False and everything is True. Mod(number,2) will return 0 or 1 thus being False or True respectively. I often use this feature to Green Stripe (or as we used to call it ETP Elephant Toilet Paper) a sheet as so:

46874-ModZeddy

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Hi RG

I needed two conditional formatting rules to get two different cell-colour backgrounds
(bearing in mind that we also have the ‘grey’ rows in rows 57 to 61)

So, you are correct..
..but, I still needed to differentiate between these two results in order to get the two different cell colours i.e. I could have used these two rules:
Rule1:
=AND(S6>0,G6>95000000,MOD(ROW(G6)) ‘for colour1 background

Rule2:
=AND(S6>0,G6>95000000,NOT(MOD(ROW(G6))) ‘colour2 background

zeddy

Reply | Quote

The row color depends ONLY on the day of the week in Column D, or on whether the drawing is for MM (Mega Millions) or PowerBall (PB) in Column C. MM and PB drawings always take place on the same days of the week… Tue and Fri for MM, and WED and SAT for PB.

The numbers in Column S vary from workbook to workbook, as Dwg #1 can come on any of the four days of the week in Column D.

I didn’t make that clear initially. Nor did I make it clear that the “grayed out” rows are made so by me, to eliminate days on which no drawing takes place. In the example, the lottery pool is finished on 03/31/17, so the days after that are grayed out.

Basically the grid is set up for 14 calendar weeks. The dates and the drawings are fit into it, rather than the other way round, and dates without drawings for the current pool are grayed out manually by the Chief Lottery Officer, which is me.

Once again I learn the age-old lesson that what is obvious to me isn’t necessarily so to anyone else.

Reply | Quote

I must be missing something here as I don’t actually see the problem. If you simply change the formatting rules that currently make the cell green so that they don’t apply a fill but change the font instead, you don’t have to worry about the cell fill.

Reply | Quote

You’re not missing something, *I* am! I couldn’t see how to change the rule so it didn’t change the fill color.

With your assurance that it could be done, I just Cleared the font color in the rule, and now I have what I want.

Sorry for putting the loungers through all this trouble. Live and learn.

Reply | Quote

Hi Lou

oops! my apologies too!
..my mind was on other things!
I was going to post an update to correct this when I got back from hospital – Rory beat me to it!

I’m such a plonker! I had (wrongly) assumed that in order to change the font colour when you also had different cell colours, you needed to include the cell fill-colours in the conditional formatting rule. You don’t.

Apologies to RG too!

zeddy

Reply | Quote

To be fair to all of us, it isn’t exactly intuitive that there’s a way to Clear a format you have set. The Clear button is kind of out of the way, is usually grayed out, and only becomes active when you’ve formatted some aspect of the Rule.

Reply | Quote