Previous Topic Next Topic

Formatting cells based on certain criteria

Solved/Closed
sailingdemon Posts 11 Registration date Thursday September 28, 2017 Status Member Last seen November 13, 2017 - Sep 28, 2017 at 02:06 PM
sailingdemon Posts 11 Registration date Thursday September 28, 2017 Status Member Last seen November 13, 2017 - 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!

2 responses

Answer 1 / 2
Blocked Profile
Sep 28, 2017 at 05:54 PM
Have you looked at conditional formatting?
https://ccm.net/forum/affich-910435-conditional-formating-formula-cell-text-change-color
Or...
https://support.microsoft.com/en-us/office/use-conditional-formatting-to-highlight-information-fed60dfa-1d3f-4e13-9ecb-f1951ff89d7f?ui=en-us&rs=en-us&ad=us

0
sailingdemon Posts 11 Registration date Thursday September 28, 2017 Status Member Last seen November 13, 2017
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.
0
Blocked Profile
Updated on 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.
0
sailingdemon Posts 11 Registration date Thursday September 28, 2017 Status Member Last seen November 13, 2017 > Blocked Profile
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,
0
Blocked Profile
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!
0
sailingdemon Posts 11 Registration date Thursday September 28, 2017 Status Member Last seen November 13, 2017 > Blocked Profile
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
0
Answer 2 / 2
Blocked Profile
Sep 29, 2017 at 06:43 PM 
end by my new boss!


Well, we will assist with you avoiding the dreaded RPE!
0

Similar discussions

help me to add particular cells
mathewmunna -
mathew munna -

2 responses
Fill adjacent cell with number from text answer
MitchP -
vcoolio -

20 responses
Excel function question
1234ah -
ac3mark -

3 responses
Excel - IF function with multiple words
Nick -
user -

14 responses
Highlight cell if another cell contains specific text
Abbi -
vcoolio -

1 response