Coloring by Excel VBA Function

[xlStar]

I am trying to build a Function that return with a message plus color coding.

The Function does work in term of return the necessary string and it does set the Conditional Formatting criteria BUT not the colours. On the Condtional Formatting box, its stated "No Format Set".

Why can't the Function set the colors whereas if I move the Conditional Formatting VBA to a seperate SUB it runs perfect but not even when the Function call that Sub!

Here is my codes...

Public Function CHECKVAT(OneCell As Range) As String
Application.Volatile True

If Range(OneCell.Address).Offset(, -1).Value = "NX" Then
CHECKVAT = "ok"
Else
If Range(OneCell.Address).Offset(, -1).Value = "SX" Then
If Round(Range(OneCell.Address).Value / 1.175, 2) - Round(Range(OneCell.Address).Offset(, -4).Value, 2) = 0 Then
CHECKVAT = "ok"
Else
CHECKVAT = "#ERR: Invalid 17.5% VAT Amount"
End If
Else
If Range(OneCell.Address).Offset(, -1).Value = "FX" Then
If Round(Range(OneCell.Address).Value / 1.05, 2) - Round(Range(OneCell.Address).Offset(, -4).Value, 2) = 0 Then
CHECKVAT = "ok"
Else
CHECKVAT = "#ERR: Invalid 0.5% Fuel VAT Amount"
End If
Else
CHECKVAT = "#ERR: Invalid Tax Area Code"
End If
End If
End If


Range(ActiveCell.Address).EntireColumn.FormatConditions.Delete
With Range(ActiveCell.Address).EntireColumn.FormatConditions _
.Add(xlCellValue, Operator:=xlEqual, Formula1:="=""#ERR: Invalid 17.5% VAT Amount""")
With .Interior
.ColorIndex = 3
End With
End With

'Range(ActiveCell.Address).EntireColumn.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""#ERR: Invalid 17.5% VAT Amount"""
'Range(ActiveCell.Address).EntireColumn.FormatConditions(1).Interior.ColorIndex = 3

Range(ActiveCell.Address).EntireColumn.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""#ERR: Invalid 0.5% Fuel VAT Amount"""
Range(ActiveCell.Address).EntireColumn.FormatConditions(2).Interior.ColorIndex = 46

Range(ActiveCell.Address).EntireColumn.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=""#ERR: Invalid Tax Area Code"""
Range(ActiveCell.Address).EntireColumn.FormatConditions(3).Interior.ColorIndex = 6


End Function

 

[GlennUK]

Hi xlStar,

besides the fact that Functions are NOT supposed to be able to make changes, you're logic is very inefficient. I'll explain what I mean ... every time that this function is referenced, you try to change the condition formatting of an entire column.

You would be better off rethinking this logic and work out when you are going to need to change the conditional formatting on the required columns, and attached a call to a sub via a worksheet event. How about worksheet_change? ( Although I still think this is overkill, as surely the conditional formatting will only really need to be done once. )

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.