CODEWITHSUNDEEP
vbaexcelconditional-formatting
Ryszard Jędraszyk
Ryszard Jędraszyk

Reputation: 2412

VBA cell conditional vs manual formatting - best practices

I have a function in VBA to format cell interior color based on a specified value - negative and positive numbers and zeros.

PositiveFillColor, NeutralFillColor and NegativeFillColor are global long variables from reading color put into settings sheet cell.

My main concern is speed of macro (which for moderate amount of data apparently seems to be very good) and workbook size (3,5 MB seems to be too much for this amount of data).

Maybe it's a better practice to use Excel conditional formatting with VBA?

Public Function FillColorByValue(ByVal RefNumber As Double) As Long

Dim FillColor As Long

    If RefCellValue > 0 Then
        FillColor = PositiveFillColor
    ElseIf RefCellValue = 0 Then
        FillColor = NeutralFillColor
    ElseIf RefCellValue < 0 Then
        FillColor = NegativeFillColor
    End If

FillColorByValue = FillColor

End Function

Upvotes: 0

Views: 138

Answers (1)

Doug Coats
Doug Coats

Reputation: 7117

Try this with both ways and see which one is faster

sub thetimingstuff()

Dim StartTime As Double
Dim SecondsElapsed As Double

 StartTime = Timer

'your code goes here

SecondsElapsed = Round(Timer - StartTime, 2)
MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation

end sub

Upvotes: 0

Related Questions