0

I'm trying to code a very simple little function in VBA for Excel and it has not gone well. I don't have consistent access to Excel so I can only work on it here and there. Debugging has been hell and only managed to get it working through the VBA Excel editor by creating a sub and running it through that. I've only started using VBA the last few weeks and am well out of my depth here so I'm probably missing something quite obvious any help with any part of this would be welcome

Public Function CellColorer(ByRef dayNo As Range)
Dim checkDate As Date
Dim table1 As ListObject

    table1 = ActiveWorkbook.Sheets("Sheet1").ListObjects("Table1")
    checkDate = CDate(CStr(dayNo.Range(1, 1).Value) + CStr(Evaluate(ActiveWorkbook.Names("MoMonth"))) + CStr(Evaluate(ActiveWorkbook.Names("MoYear"))))
    
    Dim i As Integer
    For i = 1 To table1.ListRows.Count
        If table1.DataBodyRange(i, 1) < checkDate & table1.DataBodyRange(i, 2) > checkDate Then
            Application.ThisCell.Interior.Color = table1.DataBodyRange(i, 1).Interior.Color
            CELLCOLOURER = ""
            Return
        ElseIf table1.DataBodyRange(i, 1) = checkDate Then
                Application.ThisCell.Interior.Color = table1.DataBodyRange(i, 1).Interior.Color
                CELLCOLOURER = table1.DataBodyRange(i, 0) + "START!"
                Return
        ElseIf table1.DataBodyRange(i, 2) = checkDate Then
                Application.ThisCell.Interior.Color = table1.DataBodyRange(i, 1).Interior.Color
                CELLCOLOURER = table1.DataBodyRange(i, 0) + "DUE!"
                Return
    Next i

End Function
Sub jili() 'I'm using this code to debug I couldn't figure out how to do so without it
CellColorer (ActiveWorkbook.Worksheets(2).Range("E6"))
End Sub

The main goal of the code is too check a date specified by the user between a start date and end date. If it is in the inclusive range it should change the cells color to match that tables color as well as displaying some text if it's the start or the end of the project.

DWaterston
  • 1
  • 1
    A UDF called from the worksheet can only return a value to the cell from which it is called. It cannot, by itself, change the format of the cell that called it or any other cell. Now there is a [WorkAround](https://stackoverflow.com/questions/23433096/using-a-udf-in-excel-to-update-the-worksheet/23437280#23437280). That being said, a better method would be to do the formatting through conditional formatting based on the value returned from the UDF – Scott Craner Sep 09 '22 at 17:15
  • It sounds like you are describing [Conditional Formatting](https://support.microsoft.com/en-us/office/use-conditional-formatting-to-highlight-information-fed60dfa-1d3f-4e13-9ecb-f1951ff89d7f) – HackSlash Sep 09 '22 at 17:54
  • I couldn't figure out how to get conditional formating to do what I needed, the goal of this is to update automaticall based on a table in another sheet. If I can link this into conditonal formatting I'd be happy to, if someone could put me in that direction. I haven't found anyway to do this – DWaterston Sep 11 '22 at 14:31

0 Answers0