VBA Format Cells Based On Data

[ctstewart308]

Greetings! I'm a real newbie to VBA and I have been stumped by this formatting of cells. I have Excel files (lists for gages due calibration) that we post for production. These lists are Excel files generated daily by our gage management software. I have a macro that opens each file and saves it as an HTML file. During the time the Excel file is open I want to date and time stamp the file in cell G1. I want to examine column D and format the cells based on the following criteria, if the date in the cell is on or before today, fill the cell red. If there is no date I want to fill the cell yellow. I have the code below and for some reason the date code isn't working. I've tested it in another Excel macro without all the other code and it works. Also, i get an error "End if without block if" on the cell color fill code. Help please.

Private Sub Workbook_Open()


ChDir "C:\Users\UIDP3005\Documents\Macros"
        Workbooks.Open Filename:="C:\Users\UIDP3005\Documents\Macros\TESTCOLOR.xls"
        
            Dim G1 As Date
            With Selection
            .Value = Now
            .NumberFormat = "m/d/yyyy h:mm:ss AM/PM"
            End With
            
            Dim D As Date
            Columns(D).Font.Color = vbBlack
            For D = 1 To Rows.Count
            Next D
            
            If Cells(D).Value <= ("today") And Not IsEmpty(Cells(D)) Then Cells(D).Font.Color = vbRed
            If Cells(D).IsEmpty(Cells(D)) Then Cells(D).Font.Cell = vbYellow
            End If
                   
   ActiveWorkbook.SaveAs Filename:= _
        "C:\Users\UIDP3005\Documents\Macros\TESTCOLOR.htm", FileFormat:=xlHtml, _
        ReadOnlyRecommended:=False, CreateBackup:=False
    Application.Wait Time + TimeSerial(0, 0, 2)
    ActiveWorkbook.Close
    


End Sub

 

[RCBricker]

Greetings! I'm a real newbie to VBA and I have been stumped by this formatting of cells. I have Excel files (lists for gages due calibration) that we post for production. These lists are Excel files generated daily by our gage management software. I have a macro that opens each file and saves it as an HTML file. During the time the Excel file is open I want to date and time stamp the file in cell G1. I want to examine column D and format the cells based on the following criteria, if the date in the cell is on or before today, fill the cell red. If there is no date I want to fill the cell yellow. I have the code below and for some reason the date code isn't working. I've tested it in another Excel macro without all the other code and it works. Also, i get an error "End if without block if" on the cell color fill code. Help please.

Private Sub Workbook_Open()


ChDir "C:\Users\UIDP3005\Documents\Macros"
        Workbooks.Open Filename:="C:\Users\UIDP3005\Documents\Macros\TESTCOLOR.xls"
        
            Dim G1 As Date
            With Selection
            .Value = Now
            .NumberFormat = "m/d/yyyy h:mm:ss AM/PM"
            End With
            
            Dim D As Date
            Columns(D).Font.Color = vbBlack
            For D = 1 To Rows.Count
            Next D
            
            If Cells(D).Value <= ("today") And Not IsEmpty(Cells(D)) Then Cells(D).Font.Color = vbRed
            If Cells(D).IsEmpty(Cells(D)) Then Cells(D).Font.Cell = vbYellow
            End If
                   
   ActiveWorkbook.SaveAs Filename:= _
        "C:\Users\UIDP3005\Documents\Macros\TESTCOLOR.htm", FileFormat:=xlHtml, _
        ReadOnlyRecommended:=False, CreateBackup:=False
    Application.Wait Time + TimeSerial(0, 0, 2)
    ActiveWorkbook.Close
    


End Sub

Try this. I re-wrote it so that it is easier to read and works with my dummy data

Private Sub Workbook_Open()
Dim strDATE As String
Dim ws As Worksheet
Dim wb As Workbook
Dim rng As Range, cell As Range
Dim lngrow As Long, lngcol As Long, L As Long
Dim i As Integer

ChDir "C:\Users\UIDP3005\Documents\Macros"
Workbooks.Open Filename:="C:\Users\UIDP3005\Documents\Macros\TESTCOLOR.xls"
        
    Set wb = ThisWorkbook
    Set ws = ActiveSheet
    
    strDATE = Now
    With ActiveSheet.Cells(1, 7)
        .Value = strDATE
        .NumberFormat = "m/d/yyyy h:mm:ss AM/PM"
    End With
    ws.Columns("D").Font.ColorIndex = xlAutomatic
    With ws
        lngrow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
    End With
    For L = 1 To lngrow
        If Cells(L, "D") = "" Then
            Cells(L, "D").Interior.color = 65535
        Else
            If Cells(L, "D").Value <= Date Then
                Cells(L, "D").Interior.color = 255
            End If
        End If
    Next
                   
   wb.SaveAs Filename:= _
        "C:\Users\UIDP3005\Documents\Macros\TESTCOLOR.htm", FileFormat:=xlHtml, _
        ReadOnlyRecommended:=False, CreateBackup:=False
    Application.Wait Time + TimeSerial(0, 0, 2)
    ActiveWorkbook.Close
    
End Sub

 

[ctstewart308]

Thank you for the attempt. I get a saved HTML file with the date in cel G1, but none of my other data in the spreadsheet is in the HTML file.

Greetings! I'm a real newbie to VBA and I have been stumped by this formatting of cells. I have Excel files (lists for gages due calibration) that we post for production. These lists are Excel files generated daily by our gage management software. I have a macro that opens each file and saves it as an HTML file. During the time the Excel file is open I want to date and time stamp the file in cell G1. I want to examine column D and format the cells based on the following criteria, if the date in the cell is on or before today, fill the cell red. If there is no date I want to fill the cell yellow. I have the code below and for some reason the date code isn't working. I've tested it in another Excel macro without all the other code and it works. Also, i get an error "End if without block if" on the cell color fill code. Help please.

Private Sub Workbook_Open()


ChDir "C:\Users\UIDP3005\Documents\Macros"
        Workbooks.Open Filename:="C:\Users\UIDP3005\Documents\Macros\TESTCOLOR.xls"
        
            Dim G1 As Date
            With Selection
            .Value = Now
            .NumberFormat = "m/d/yyyy h:mm:ss AM/PM"
            End With
            
            Dim D As Date
            Columns(D).Font.Color = vbBlack
            For D = 1 To Rows.Count
            Next D
            
            If Cells(D).Value <= ("today") And Not IsEmpty(Cells(D)) Then Cells(D).Font.Color = vbRed
            If Cells(D).IsEmpty(Cells(D)) Then Cells(D).Font.Cell = vbYellow
            End If
                   
   ActiveWorkbook.SaveAs Filename:= _
        "C:\Users\UIDP3005\Documents\Macros\TESTCOLOR.htm", FileFormat:=xlHtml, _
        ReadOnlyRecommended:=False, CreateBackup:=False
    Application.Wait Time + TimeSerial(0, 0, 2)
    ActiveWorkbook.Close
    


End Sub

 

[RCBricker]

Thank you for the attempt. I get a saved HTML file with the date in cel G1, but none of my other data in the spreadsheet is in the HTML file.

I am not certain why that is happening. It works fine on my end. this is tested and works.

 

[ctstewart308]

Would it matter if I'm using office 2016?

 

[ctstewart308]

So I've trimmed the task to just adding the date for now to see how my files are interacting and I have a new issue. When my Excel files are saved out of the gage software they appear to be formatted as tables. So the term "Dim G1 as Date" is not going to cell G1 to enter the current date and time. The excel file opens and the date is placed into cell A1. Seems this cell is the default for when the file is opened. If I open the Excel file and move my cursor into cell G1 and save the Excel file the code then works.

Any ideas about this...thanks in advance!!!!