Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Colour Cells Conditionally From Formula

1. I agreed to these rules
Join Date
7th August 2008
Posts
5

## Colour Cells Conditionally From Formula

I am trying to colour cells conditionally in my spreadsheet. I know you can do this with Conditional Formatting but I will need quite a few colours when I get right into it. The code below works if I just enter the value into cell AG5, but the value in the cell will be calculated via a formula. I imagine it is pretty easy to fix but I am pulling my hair out and would really appreciate a pointer in the right direction.

Thanks
Theo.

VB:
```Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("AG5")) Is Nothing Then
Select Case Target.Value
Case 1 To 4
icolor = 43
' Case 6 To 10
' icolor = 12
Case Else
icolor = 5
End Select

Target.Offset(0, -13).Interior.ColorIndex = icolor
End If

End Sub

```

Excel Video Tutorials / Excel Dashboards Reports

2. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,678

## Re: Colour Cells Conditionally From Formula

Hi,

Put the code in between

VB:
```Private Sub Worksheet_Calculate()

End Sub

```
HTH

3. I agreed to these rules
Join Date
7th August 2008
Posts
5

## Re: Colour Cells Conditionally From Formula

I did that and now I'm getting the following error:

VB:
```Run-time error '424':

Object required

```

Excel Video Tutorials / Excel Dashboards Reports

4. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,678

## Re: Colour Cells Conditionally From Formula

Hi,

Try

VB:
```Dim Rng As Range, r As Range

Set Rng = Range("T5:T25") 'adjust the range

For Each r In Rng
r.Interior.ColorIndex = Evaluate("LOOKUP(" & r.Offset(, 13).Address(0, 0) & _
",{0,6,13},{43,12,5})")
Next

```

5. I agreed to these rules
Join Date
7th August 2008
Posts
5

## Re: Colour Cells Conditionally From Formula

Kris,

You hit the nail on the head!
Thanks very much for your help.

Theo.
Auto Merged Post Until 24 Hrs Passes;

I have another question regarding this...

Would it be possible to include these kinds of calculations in this?

VB:
```=And(\$BG\$2>=7,\$BG\$2<=13)

```
Last edited by Theodjinn; August 8th, 2008 at 20:07. Reason: Auto Merged Doublepost

Excel Video Tutorials / Excel Dashboards Reports

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno