CODEWITHSUNDEEP
excelvbaexcel-formulabackground-color
Yellowstone
Yellowstone

Reputation: 11

Changing cell colour background in Excel using a public VBA function - not a VBA programmer

I want to change the fill colour of a cell, based on a value in another cell. This will be a series implementation, e.g. A1, based on value in B1; A2, based on value in B2 and so on.

I want to use a public function so I can use it this way and pull down for the series implementation.

IF(B1>0,ColourCell("A1","Green"),IF(B1<0,ColourCell("A1","Red",ColourCell("A1","White"))))

I don't know data types, etc. in VB - can I have some help please to get it working in VB. I have inserted a module (General). This is what the function is.

Public Function ColourCell(cell, colour)

If colour = "Green" Then

    Range("cell").Interior.Color = vbGreen

ElseIf colour = "Red" Then

    Range("cell").Interior.Color = vbRed

ElseIf colour = "White" Then

    Range("cell").Interior.Color = vbWhite

End If

End Function

My understanding is that once it all works, I can use it in the way described.

Thanks

Upvotes: 1

Views: 90

Answers (1)

SJR
SJR

Reputation: 23081

To circumvent the problem mentioned by BigBen here is another approach.

You need to remove the quotes around cell as otherwise it will be treated as a literal string.

Good practice too to declare argument types in your function.

There's no error trapping here, e.g. for invalid ranges.

Sub ColourCell(cell As String, colour As String)

If colour = "Green" Then
    Range(cell).Interior.Color = vbGreen
ElseIf colour = "Red" Then
    Range(cell).Interior.Color = vbRed
ElseIf colour = "White" Then
    Range(cell).Interior.Color = vbWhite
End If

End Sub

Call the sub this way:

Sub x()

ColourCell "A1", "Green"

End Sub

Upvotes: 2

Related Questions