Format Ranged Cells Based on Value - VBA help

[inkspot]

Hi,

I've been reading some books on VBA and having trouble grasping the concept of ranges...

I'm trying to have 5 columns, A-E, column E is named Status.
If column E's value = "closed" Then column A-D's interior color change to grey.
And if not, change to transparent. (or later on...other status colors)

How can I write the codes so this color change will automatically happen?...

Thankssssssssss...

[Bob Phillips]

Add this to the worksheet code module

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Me.Columns("E")) Is Nothing Then

With Target

If .Value = "closed" Then

.Offset(0, -4).Resize(, 4).Interior.ColorIndex = 15
Else

.Offset(0, -4).Resize(, 4).Interior.ColorIndex = xlColorIndexNone
End If
End With
End If
End Sub

[inkspot]

Thankssss. When I pasted the code, it says Runtime 424, object required. Isn't the object the cell in which I have the "closed" value?

[Bob Phillips]

Did you add the code to the worksheet code module?

[snb]

A range in a worksheet can be any set of cells in the worksheet:
- it can be 1 cell
- it can be all cells in the worksheet
- it can be a column (or part of it)
- it can be a row (or part of it)
- it can be adjacent cells or dispersed cells

If you look at a Range as an 'area' that's what it is.

to answer your question you could use:


Private sub worksheet_change(ByVal Target As Range)
if target.column=5 then target.offset(,-4).resize(,4).interior.colorindex=15 * Abs(target.Value = "closed")
end sub

[inkspot]

I found out what's going on...probably. I had codes in other worksheets too which probably created a conflict.

I opened up a new one and now it works. Gonna study how the code works.

Thank you!!

[inkspot]

A range in a worksheet can be any set of cells in the worksheet:
- it can be 1 cell
- it can be all cells in the worksheet
- it can be a column (or part of it)
- it can be a row (or part of it)
- it can be adjacent cells or dispersed cells

If you look at a Range as an 'area' that's what it is.

to answer your question you could use:


Private sub worksheet_change(ByVal Target As Range)
if target.column=5 then target.offset(,-4).resize(,4).interior.colorindex=15 * Abs(target.Value = "closed")
end sub


Thank you for the explanation. I'll try to treat it as an area. :yes

[Bob Phillips]

Gonna study how the code works.

It is very simple, it just checks if the cell being changed (Target) is within column E, and if the value is set as closed, in which case it colours he cell, otherwise it clears any colour.