Report

Formatting cells based on certain criteria [Solved]

Ask a question sailingdemon 4Posts Thursday September 28, 2017Registration date September 29, 2017 Last seen - Last answered on Sep 29, 2017 at 06:46 PM by sailingdemon
I want to make a cell change colour based on a number of different criteria:
Cell A1 Has an incident date
B1 Displays the date 14 days after the incident date.
Cell C1 will have a car make in, Cell D1 has an examined yes or no option. C1 and D1 will always be completed.
However there may be a car in Cell E1, G1, I1, with examined options in cells F1, H1, J1.

I would like cell B1 to be formatted in the following ways:
Less than 2 days from todays date until due date - red fill
3 to 7 days from todays date until due date - orange fill
8 to 14 days from todays date until due date - green fill

But not if D1 is YES (blank or No then must change), and there is nothing entered in E1, G1 I1.

If E1, G1 or I1 have a car entered then the corresponding examined box must be in the same state as described for D1.

Is this possible? if so how please?
I know its a little complicated!
Helpful
+0
plus moins
sailingdemon 4Posts Thursday September 28, 2017Registration date September 29, 2017 Last seen - Sep 29, 2017 at 05:37 AM
Thank you, yes I have looked at this, it seems fine for changing the cell colour however there are so many conditions that it needs something else I think. I am not that familiar with this area of Excel, I have just bought myself conditional formatting and the basics of writing macros - in this particular sheet I have managed to include a 'button' which when clicked collates data from 12 different cells and sends it as an email.

Still working on this problem though.
Reply
ac3mark 7998Posts Monday June 3, 2013Registration date ModeratorStatus October 17, 2017 Last seen - Sep 29, 2017 at 06:13 PM
It all just formulas. Then you rank the formulas as the least to most. The winning formulas, after the whole list is processed, is acted upon in terms of where it fits. Does that make sense?

If you have 50 different shaded of gray, all with different values, you could perform this. Just start from the least significant, in this case .00-.99, then 1 to 1.99, then 2 though 2.99. Sometimes significance isnt' really a numeric value, but a number of different cases, so for this we would look to such formulas as:
=if(and(a1=yes,b1=yes)) = GREEN CELL (as far as we are concerned, every cell should be a GREEN CELL)

Then our next formula checks to disqualify the green cells, as in:
=if(and(a1=yes,b1=no)) = RED CELL

Then we would check for the next qualifying (in this case, disqualifying), but this time we will turn it yello, because it can still be green, and it it isn't RED. A YELLOW CELL maybe found with:
=if(and(a1=no,b1=yes)) = YELLOW CELL


in this particular sheet I have managed to include a 'button' which when clicked collates data from 12 different cells and sends it as an email.


Is this where you are having problems? Let us know so you can expand on the scope of the project.
Reply
sailingdemon 4Posts Thursday September 28, 2017Registration date September 29, 2017 Last seen - Sep 29, 2017 at 06:36 PM
Thanks, I am new to all this - been dropped in at the deep end by my new boss!
what you have written makes logical sense and taking this I am going to try and assemble it all into a coherent (and working) set of conditional formatting.

I have a week away from it all, so will try on my return. Thanks again,
Reply
ac3mark 7998Posts Monday June 3, 2013Registration date ModeratorStatus October 17, 2017 Last seen - Sep 29, 2017 at 06:40 PM
If you need help, I will help if you present some effort. Please do not ask me to code a turn key, put I will "pull it from the mud!"

Have fun!
Reply
sailingdemon 4Posts Thursday September 28, 2017Registration date September 29, 2017 Last seen - Sep 29, 2017 at 06:46 PM
No a little guidance and pointing in the general direction works best for me, having gone from using the absolute basics of excel (simple formulas to total columns, cells, averaging etc.) to using macros in a short space of time has been a steep learning curve. This is the first help I've had to ask for.

Thanks again
Reply
Leave a comment
Helpful
+0
plus moins
end by my new boss! 


Well, we will assist with you avoiding the dreaded RPE!
Leave a comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!