Add Cell Background Color Macro

boldcode · Aug 3, 2011

Hello,

I want to create a Macro that will assign a cell background color to cells in Column A that have following values within the cells (I know you can use conditional formatting, but I want to use VBA code):

Value: Team Red
Color: 3 (red, using vba color palette)

Value: M&A
Color: 6 (yellow, using vba color palette)

Value: People
Color: 15 (grey, using vba color palette)

Thanks for the help,

BC

Trevor G · Aug 3, 2011

Try this out

Sub col()
Dim cell As Range
For Each cell In Range("A1:A500")
If cell.Value = "Team Red" Then
cell.Font.Color = vbRed
ElseIf cell.Value = "M&A" Then
cell.Font.Color = vbYellow
ElseIf cell.Value = "People" Then
cell.Font.Color = ColorIndex = 16
End If

Next
End Sub

Back colour

Sub col()
Dim cell As Range
For Each cell In Range("A1:A500")
If cell.Value = "Team Red" Then
cell.Interior.Color = vbRed
ElseIf cell.Value = "M&A" Then
cell.Interior.Color = vbYellow
ElseIf cell.Value = "People" Then
cell.Interior.Color = ColorIndex = 16
End If

Next
End Sub

And back colour using ColorIndex

Sub col()
Dim cell As Range
For Each cell In Range("A1:A500")
If cell.Value = "Team Red" Then
cell.Interior.ColorIndex = 3
ElseIf cell.Value = "M&A" Then
cell.Interior.ColorIndex = 6
ElseIf cell.Value = "People" Then
cell.Interior.Color = ColorIndex = 16
End If

Next
End Sub

boldcode · Aug 3, 2011

Trevor G,

How can replace Range(A1:A500) so the range is the entire column?

Trevor G · Aug 3, 2011

Just use Range("A:A")

boldcode · Aug 3, 2011

Trevor G,

Thanks you, it works great!!!

Trevor G · Aug 3, 2011

Please to read you have a solution

Domenic · Aug 3, 2011

Also, the range can be restricted to the used range...

Code:

Intersect(ActiveSheet.UsedRange, Range("A:A"))

boldcode · Aug 3, 2011

Trevor G and Domenic,

Thank you first of all for your help. I am using this piece of code against a pivot table. The code you produced works great, but I now would like to tweak it just a bit by explaining the following:

I have three column fields in my pivot table. The first column field is found in Cell A9 and what I would like to do is not only change the cell background color of the cell that contains "M & A" which is Yellow currently, but also change the cell background color to yellow for all the cells right under "M & A" to yellow that are associated to the value "M & A"

For example:

if the value of "M & A" is in cell A10 then all the cells under cell A10 (e.g. A11, A12, etc...) that are grouped with "M & A" would have a background color of yellow

Add Cell Background Color Macro

boldcode

Active Member

Excel Facts

Trevor G

Well-known Member

boldcode

Active Member

Trevor G

Well-known Member

boldcode

Active Member

Trevor G

Well-known Member

Domenic

MrExcel MVP

boldcode

Active Member

Similar threads

Forum statistics

Share this page

Add Cell Background Color Macro

Active Member

Excel Facts

Well-known Member

Active Member

Well-known Member

Active Member

Well-known Member

MrExcel MVP

Active Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock