Formatting cells based on certain criteria [Solved]
sailingdemon11
Posts Thursday September 28, 2017Registration date
November 13, 2017
Last seen
-
Sep 28, 2017 at 02:06 PM - Latest reply: sailingdemon11
Posts Thursday September 28, 2017Registration date
November 13, 2017
Last seen
- Sep 29, 2017 at 06:46 PM
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!
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.
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.
sailingdemon11
Posts Thursday September 28, 2017Registration date
November 13, 2017
Last seen
>
ac3mark9571
Posts Monday June 3, 2013Registration dateModeratorStatus
June 19, 2018
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,
sailingdemon11
Posts Thursday September 28, 2017Registration date
November 13, 2017
Last seen
>
ac3mark9571
Posts Monday June 3, 2013Registration dateModeratorStatus
June 19, 2018
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.
Still working on this problem though.
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
Is this where you are having problems? Let us know so you can expand on the scope of the project.
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,
Have fun!
Thanks again