Any people good with Excel Conditional Fomartting?

[Ed.200]

Hi all.

Been trying to work on something for a few weeks but reached a point with Conditional Formatting and its formula's to get things to work correctly.

A quick synopsis.
I have a large spreadsheet containing information about equipment that requires calibration at certain intervals, 3 months 6 months yearly etc.
I have 2 columns (amongst all the rest) which have the date it was last done, and a predicted date (using the =Edate 6 function to predict the date in 6 months)

I am trying to get the conditional formatting to change the background colour of the row to:

White when it is fine and in date,
Orange when calibration is due in that month
Red when it is overdue (1 month or older)
​

e.g
A piece is due in September is white, as it is not yet due.
A piece in August is due this month and therefore Orange
A piece in July is overdue and therefore Red.

The predicted future date (=edate) may well be irrelevant I have tried versions with and without that particular function in the formula.

I have tried using the =today function to get excel to calculate from the current day as to how things should be, but that dosent seem to have worked.

Just got to a point that I may be overcomplicating it, or missing something completely, just hit abit of a wall with it.
Any help is much appreciated

Ed

 

[Strell]

I always have a bit of a faff when trying to do it all within a conditional format function.
What I would do is:

Create a column with the predicted date (can use the date add functions, described here)
Do a date diff function between the newly created column and the last done date (details here)
Then using this column you can get your three options, when its greater than 1 can be left white, if 0 then it can be amber and lastly below 0 red

Hopefully that makes sense?

If you wanted to you could then wrap that all up as a condition format function without having to create that extra column (or even the future date column too if you really wanted to)

 

[Ed.200]

Strell, Thanks for that it has been useful, as another way to do it.

I have combined the datedif function to give me a value between the current day (using =today in a locked cell) and the predicted date. This then gives me a value. The only issue now is trying to manipulate it so that it will recognise the - values, as at the moment once the today cell becomes greater than the predicted date, (making it overdue) it returns a #Num! error.
Hopeful there is a way round that to also allow the conditional formatting to apply, and then get them to apply in the correct order.

 

[Spinksy]

Strell, Thanks for that it has been useful, as another way to do it.

I have combined the datedif function to give me a value between the current day (using =today in a locked cell) and the predicted date. This then gives me a value. The only issue now is trying to manipulate it so that it will recognise the - values, as at the moment once the today cell becomes greater than the predicted date, (making it overdue) it returns a #Num! error.
Hopeful there is a way round that to also allow the conditional formatting to apply, and then get them to apply in the correct order.

Use an =IF formula? Create a second column at the side of the value columns for this formula to run along side and do something along the lines of below? Then hide the unnecessary column....

NB: I haven't checked this particular formula in excel as I am on my phone but it should work from memory

Ie: =IF(K16*>=1,"Due",IF(K16<=0,"Overdue","Not Due")

Then just add conditional formatting for different colours etc based on the formatting searching for key words (the exact ones you put in your IF formula

 

[Ed.200]

I had considered an IF function, but it seemed to be getting abit complicated, but probably no more complicated than I have made it now.
Unfortunately excel is not my strong point, it is very capable software, its just knowing how to use it lol!

Have now managed to get most of it to apply, but due to the use of formulas, and excel not liking negative dates i get #NUM! errors, but I have just got a condition for those to become another colour meaning they are very outdated.

Any tips on how to get the formatting to apply across the entire rows?
I have followed a few guides online, I have all the cells in the "apples to" section under conditional formatting, but it still only highlights the cell. I understand this may be the way I am applying the conditional formatting using - Cell Value Contains Overdue, Due, etc. As these only appear in one column, therefore are the ones that the formatting applies to.
I just wondered if there was a away to highlight the whole row when one cell meets the conditions?

 

[Oggy997]

MAybe adapt VBA to do it?

I'm just going to bed but I use VB to highlight a row based on value, might be easily tweaked for your scenario if you spend a bit of time, will look back when at worktomorrow to apply the cond. format normally when I am not so tired.

Dim rrng As Range
Dim rcell As Range
    Set rrng = Range("A13:Q300")
    For Each rcell In rrng
    If UCase(rcell.Text) = "REPLEN" Then
    rcell.Select
    thisrow = ActiveCell.Row
    Range("A" & thisrow & ":Q" & thisrow).Interior.ColorIndex = 8
    
    End If
    If UCase(rcell.Text) = "HIGH" Then
    rcell.Select
    thisrow = ActiveCell.Row
    Range("A" & thisrow & ":Q" & thisrow).Interior.ColorIndex = 7
    
    End If
    Next rcell

 

[Ed.200]

I think it is possible to apply the same things through VBA. Unfortunately I know less about VBA than I do about excel so things just seem more complicated.

I have managed to do 90% of what I wanted and see what the response is. Might fettle with it some more guy on the whole I think it's done!

Thanks everyone for your contributions!

 

[Oggy997]

Can you send me the file ? I'll play about more purposefully with your source that i would in a random spreadsheet - I've never used =EDATE until today :/

 

[zakir500]

Which file do you want to format?