Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

Formatting cells based on certain criteria [Solved/Closed]

Report
Posts
11
Registration date
Thursday September 28, 2017
Status
Member
Last seen
November 13, 2017
 -
Posts
11
Registration date
Thursday September 28, 2017
Status
Member
Last seen
November 13, 2017
 -
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!
See more

2 replies

Answer 1 / 2

Have you looked at conditional formatting?
https://ccm.net/forum/affich-910435-conditional-formating-formula-cell-text-change-color
Or...
https://support.office.com/en-us/article/use-conditional-formatting-to-highlight-information-fed60dfa-1d3f-4e13-9ecb-f1951ff89d7f?ui=en-US&rs=en-US&ad=US

Report
Posts
11
Registration date
Thursday September 28, 2017
Status
Member
Last seen
November 13, 2017
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.
Report
Blocked Profile
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.
Report
Posts
11
Registration date
Thursday September 28, 2017
Status
Member
Last seen
November 13, 2017
 > Blocked Profile
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,
Report
Blocked Profile
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!
Report
Posts
11
Registration date
Thursday September 28, 2017
Status
Member
Last seen
November 13, 2017
 > Blocked Profile
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
Answer 2 / 2 

end by my new boss!


Well, we will assist with you avoiding the dreaded RPE!
Ask a question