What's wrong with this formula?

Gem7321

I need an excel whizz please!

I have a date in cell G19, in another cell I want it to turn green if the date in G19 is less than 90 days from today, to turn amber if it's less than 30 days from today, and to turn red if it's less than 14 days from today. If it's more than 90 days it can just stay white.

I've used the following nested if statement:

=IF(G19-TODAY()<90,"chase", IF(G19-TODAY()<30, "mark", IF(G19-TODAY()<14, "check", " ")))

And conditional formatting to turn cells green if they say 'chase', to turn them amber if they say 'mark' and to turn red if they say 'check'.

I didn't use conditional formatting on its own as we want to be able to type over the cell with the formula for it to remove all conditional formatting.

But, regardless of the date my cell stays green unless it's more than 90 days then it stays white.

Help!

PGM

try this;

=IF(TODAY()-G19>90,"chase", IF(TODAY()-G19>30, "mark", IF(TODAY()-G19>14, "check", " ")))

Gem7321

Thanks PGM sorted now - my formula was back to front (thanks Beccalou( so now this is working

=IF(G19-TODAY()<14,"chase", IF(G19-TODAY()<30, "mark", IF(G19-TODAY()<90, "check", " ")))

jamieleeuk

You could use VBA to automatically change the colour of a cell based on the word entered into that cell -

Go into the spreadsheet and the particular sheet where the cell colour change is to happen, either press Alt+F11 or right-click on the tab name and choose "View Code".

At the top of the screen will be two drop down boxes, choose the left one and select "Worksheet" and now from the right hand side on choose "Change"

Put your cursor between the lines that say :

Private Sub Worksheet_Change....

End Sub

Copy this code and paste it in between the 2 lines above.

NB - you will need to change the range to suit your needs - IE, if you knew that the words CHECK, MARK and CHASE are only going to appear in one column, use that column otherwise you can choose A1: F100.

In the second example, if there was the word "CHECK" in cell E45, it would turn red.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
For Each cell In Range("E2:E500")
If cell.Value = "Check" Then
cell.Interior.Color = XlRgbColor.rgbLightGreen
ElseIf LCase(cell.Value) = "check" Then
cell.Interior.Color = XlRgbColor.rgbLightGreen
ElseIf cell.Value = "Mark" Then
cell.Interior.Color = XlRgbColor.rgbOrange
ElseIf LCase(cell.Value) = "mark" Then
cell.Interior.Color = XlRgbColor.rgbOrange
ElseIf cell.Value = "Chase" Then
cell.Interior.Color = XlRgbColor.rgbRed
ElseIf LCase(cell.Value) = "chase" Then
cell.Interior.Color = XlRgbColor.rgbRed
End If
Next cell
End Sub

All the best

PGM

Gem7321 wrote: »

Thanks PGM sorted now - my formula was back to front (thanks Beccalou( so now this is working

=IF(G19-TODAY()<14,"chase", IF(G19-TODAY()<30, "mark", IF(G19-TODAY()<90, "check", " ")))

this formula doesn't work for me, it can't do

Gem7321

Sorry Jamie that sounds far too complicated for me! I think I'll stick with conditional formatting

PGM it's working fine for me, just now that the trigger words are the other way around so chase is red, mark is still amber and check is green.

PGM

Gem7321 wrote: »

PGM it's working fine for me, just now that the trigger words are the other way around so chase is red, mark is still amber and check is green.

Is the date in G19 past or future?

Gem7321

most of them are future dates but some past, the formula still works and the cell goes red for the past dates

PGM

Gem7321 wrote: »

most of them are future dates but some past, the formula still works and the cell goes red for the past dates

Ah right, I'd assumed it was for chasing debtors, mine works for that

jamieleeuk

It's easier and more maintainable my way I would have thought. Flexibility with the range covered, no need to copy and drag down formulae. But, as my mother always said, if it aint broke, don't fix it.