• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 191
  • Last Modified:

Color Cell based on value in another column.

Hi,

On the attached sheet for every value in column L that is not zero, I would like to color the Cell in column A on that row red. Needs to be a VBA sub. The number of rows will vary day to day.

Thanks in advance,

swjtx99
0
swjtx99
Asked:
swjtx99
  • 3
  • 3
1 Solution
 
Ejgil HedegaardCommented:
Why VBA?

Conditional formatting with a formula like this =AND(LEN(A1)>0,L1<>0) on the entire column can do it.
See file.
Conditional-format-column-A.xlsx
0
 
swjtx99Author Commented:
Hi Eigil,

Because the question was asked how to do it in VBA?

After that, It's part of a larger macro that users with zero excel skills run multiple times a day on output from another system. It's just more convenient than constantly teaching a lot of people how to do conditional formatting.

Thanks for taking a look at it,

swjtx99
0
 
Ejgil HedegaardCommented:
Makes sense, here is a VBA sub to do it.

Sub Color_A_red_when_L_not_0()
    Dim ws As Worksheet, rw As Long
    
    Set ws = ActiveSheet
    ws.Columns(1).Interior.Color = xlNone
    For rw = 2 To ws.Range("A" & Cells.Rows.Count).End(xlUp).Row
        If ws.Range("L" & rw) <> 0 Then
            ws.Range("A" & rw).Interior.ColorIndex = 3
        End If
    Next rw
End Sub

Open in new window

Color-A-based-on-L.xlsm
0
Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

 
swjtx99Author Commented:
Hi Ejgil,

Works Great! Thanks for your help. I also came up with a less elegant solution that works using offset.

Sub color()

Dim cell As Range

For Each cell In Range("L2", ActiveSheet.Range("L2").End(xlDown))

If cell.Value = "1" Then _
cell.Offset(0, -11).Interior.ColorIndex = 3

Next cell
 
End Sub
0
 
swjtx99Author Commented:
Hi Ejgil,

Hmm... When I add your code to my macro, it's giving me an error "next without for" but the for is obviously there...

Any ideas?

Thanks in advance,

swjtx99
0
 
Ejgil HedegaardCommented:
It is not Next that is missing in your code, but the If statement is not closed.
Insert "End if" just before "Next cell".
Sometimes the compiler make mistakes like that.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now