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

Hi Purfleet,

I haven't thought of a macro to hide the columns but this is great, you are on the right track, yes. Now, what I would like is to coloured the cells in the red area such as I explain in the picture. 

Thanks a lot for your feedback,

Cristina 

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

Hi Purfleet,

I had to remove lines:

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

and

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

because cells were coloured according to the stock I wrote incolumn "G" but then, it dissappeared and a I were asked to enter the figures in column "G" again. Removing this two lines, it works perfect.

I would like to do the same with the below area of the document in which there are the same info but instead of showing the quantity of parts, shows the number of pallets, is it possible to add this?

And the most important question, I want to learn how to do this, what do you recommend?

Thanks a lot for your support,

Cristina

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.myonlinetraininghub.com/category/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

Hi Purfleet,

I got it!!! I got to do the same with the table below, the one that shows the number of pallets. I copied the code and paid attention to the rows they referred to and changed them accordingly. 

I will follow your advice and will start to see videos and to read the posts here in this website. I've started to think how I can improve the excel files I use everyday in my job. 

I am really thankful for your help. Thanks so much and have a nice Sunday 🙂

Cristina

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

Hi Purfleet,

The file has been working correctly at work but this morning I've tried to read something about VBA and opened the file in my computer to see the code. Unfortunaly, to my surprise, the bottom doesn't work and a message appears saying:

"Se ha producido el error "91" en tiempo de ejecución: variable de objeto o bloque with no establecido"

in English

"Error "91" has been produced in execution time: object variable or with block is not established".

In the VBA editor I see that the line affected is this one:

StartDateColAdd = Left(Range("6:6").Find(what:=Format(td, "DD-MMm"), LookIn:=xlValues).Address, 3)

but I am unable to find out what is wrong...

I've thought that maybe the reason is that today is a new month, is it possible? why it doesn't work if it has been working all the time? it is quite frustrating, to be hones 🙁

Thanks,

Cristina

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

Hi Purfleet,

It has started to work again!!!

OMG, I have so much to learn... everything is new and this is an advance level macro, at least for me.

Thanks so much again 🙂

Cristina