VBA-Change color of cells based on value in particular cell

Question

I want to change the background colors of cells A2:C2 based on the value of cell D2.

This also applies to the relative cells in rows 3,4, and 5.

If the value in cell D# is 1, I'd like color x. If the value is 2, I'd like color y, if the value is 3, I'd like the color z.

If it makes a difference, the target range (A2:D6) will be in a table format.

I'd like this subroutine to execute upon opening the workbook. I know where to put that subroutine so don't sweat instructing me how.

I've done this with conditional formatting, but it'd be nice to have some VBA I can copy-pasta into future reports.

Conditional formatting will automatically adjust. Why use VBA, which would have to run again and again and would be less efficient? Conditional formatting can also be copied into other files. — teylyn
– teylyn, Commented Feb 2, 2016 at 0:34
It'd be helpful to learn some syntax. I'm sure I could morph it into something more practical someday. Thanks. — Mark Romano
– Mark Romano, Commented Feb 2, 2016 at 0:37
What have you tried so far? Where are you stuck on your code? What are you trying to code on top of native copy-paste? — Rik Sportel
– Rik Sportel, Commented Feb 2, 2016 at 0:39
@RikSportel I don't know how to create the array/loop for a for/each statement. Thanks. — Mark Romano
– Mark Romano, Commented Feb 2, 2016 at 0:44
@teylyn Array/loop is my hunch as to how to best write the code, but it does not mean it is the best way. I don't want to limit the answerer's solutions so I choose intentionally not to be specific. Thanks. — Mark Romano
– Mark Romano, Commented Feb 2, 2016 at 0:49

Gary's Student · Accepted Answer · 2016-02-02 00:57:23Z

9

You should use Conditional formatting, but this works:

Sub ColorMeElmo() Dim i As Long, r1 As Range, r2 As Range For i = 2 To 5 Set r1 = Range("D" & i) Set r2 = Range("A" & i & ":C" & i) If r1.Value = 1 Then r2.Interior.Color = vbRed If r1.Value = 2 Then r2.Interior.Color = vbBlue If r1.Value = 3 Then r2.Interior.Color = vbYellow Next i End Sub

answered Feb 2, 2016 at 0:57

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mark Romano Over a year ago

Precisely what I needed. You're excellent, thanks for the elmo sub.

Luiz Vaughan Over a year ago

@Gary's student. I had a similar problem today and it took me some time to fix it by just using conditionals. It is okay now, but this post is precisely what I did in a 40-minute-work. Now, similar problems will take 30 seconds. Thank you too Gary.

Alan Campbell Over a year ago

Conditional formatting can give you a range between 2 colors, or even 3 colors. When you want 4 or more, you either split cells into multiple groups, or turn to code like this. Good to have.

Collectives™ on Stack Overflow

VBA-Change color of cells based on value in particular cell

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related