Conditional formatting depending on the figures of a fix column|General Excel Questions & Answers|Excel Forum|My Online Training Hub

Hi Cristina
As a first attempt

I have written a small macro that when press will check the dates in row 6 and hide anything that is older than today, it then updates the date in g6 and clears all the cells below.

I can’t work out what the basis is for the conditional formatting as some cells have a number others have a sum of 2 or more numbers - if you can let me know when you want the cells to be green I can update further.

Have a look at the attached and let me know if i am on the right track

I was also not sure of all the other workbooks, if they were relevant?

Purfleet

the conditional formatting wasn't to bad to work out, however i couldn't get it to work with an expanding range for additional dates.

So i cheated and did the comparison and formatting in VBA

Sub UpdateGrid()

Dim i As Integer
Dim td As Date
Dim dateR As Range
Dim dateC As Range
Dim LastDateColAdd As String
Dim LastDateColNum As Integer
Dim StartDateColAdd As String
Dim CondFormatR As Range
Dim CondFormatC As Range

td = Now()

'Finds last column cell address
LastDateColAdd = Cells(6, Columns.Count).End(xlToLeft).Address
'Finds last column number
LastDateColNum = Cells(6, Columns.Count).End(xlToLeft).Column

'Finds the first column to calc
StartDateColAdd = Left(Range("6:6").Find(what:=Format(td, "DD-MMm"), LookIn:=xlValues).Address, 3)

'clears anything in row 4 (temp formula)
Range("4:4").ClearContents

'Clears any colour formatting
With Range("7:14")
.Font.Color = vbBlack
.Interior.Color = xlNone
End With

'loops around rows 7 to 14
For i = 7 To 14

'formula for each row compared to columns g
Range("H4:" & Left(LastDateColAdd, 3) & 4) = "=IF(H6<TODAY(),"""",IF(SUMIF($H$6:H6,"">=""&TODAY(),$H$" & i & ":H" & i & ")<=$G$" & i & ",""Green"",""Red""))"

'set range for formatting
Set CondFormatR = Range(StartDateColAdd & i & ":" & Left(LastDateColAdd, 3) & i)

'loops through each cell and comapares to formula and either makes the cell green or the writing red
For Each CondFormatC In CondFormatR
If CondFormatC.Offset(4 - i, 0) = "Green" Then
CondFormatC.Interior.Color = vbGreen
End If

If CondFormatC.Offset(4 - i, 0) = "Red" Then
CondFormatC.Font.Color = vbRed
End If
Next CondFormatC

Next i

'Numbers for column G for testing!
'2400
'780
'702
'234
'2262
'1248
'4968
'5184

'hides Columns
'Sets date range
Set dateR = Range("h6:" & LastDateColAdd)

'Loops though all cells and compares date to hide
For Each dateC In dateR
If dateC < td - 1 Then
dateC.EntireColumn.Hidden = True
End If
Next dateC

'Updates G6 heading with todays date
Range("g6") = "Stock Day " & Format(td, "DD/MM")

'Clears data in column G
Range("g7:g14").ClearContents

'clears anything in row 4 (temp formula)
Range("4:4").ClearContents

'Ask users to update quantities
MsgBox "Please enter stock quantities in rows 7 to 14", vbInformation

End Sub

Try the attached and let me know what you think

Purfleet

Yes, thinking about it you would add the numbers and then run the macro whereas I was thinking it would be the other way away, which is a bit stupid in hindsight.

I will try to have a look at the other table later on

With regards to learning how to use VBA - you have already made a start by being able to remove the lines above.

There is so much information on this site (https://www.myonlinetraininghu...../excel-vba) and youtube but a lot depends on how best you learn. Personally, I like watching the videos on youtube and then trying to recreate it on my own and then working out how to use it in my work.

Joining this message board has helped a lot as i can see what kind of issues people have, research them and try to work out how best to resolve

Purfleet

Brilliant - looks good and is very satisfying when you can get it to work (and very frustrating when you can’t!)

I have no idea why it has stopped working, sorry.

As a work around i have put in a check on each cell in the range to test the date rather than a find - its a bit slower but on your fairly small data sets it should be okay.

For Each CC In DateSearchRange
If CC = td Then
StartDateColAdd = CC.Address
Exit For
End If
Next CC

Test the attached and let me know how you get on.

Purfleet