CODEWITHSUNDEEP
excelvbaif-statement
user1565556
user1565556

Reputation: 1

VBA Excel Issue

I am trying to write a code on excel 2003 to change the background colour of a cell based on the information I have manually entered in a previous cell. This is to show the customer satisfaction scores of our top 10 customers.

I have written this code below but I only know how to make it work for one cell and not for a range of cells. I am an amateur when it comes to using this so any help would be appreciated.

Private Sub Worksheet_Change(ByVal Target As Range)

If Range("B1").Value < Range("A1").Value Then Range("B1").Interior.ColorIndex = 3
If Range("B1").Value = Range("A1").Value Then Range("B1").Interior.ColorIndex = 6
If Range("B1").Value > Range("A1").Value Then Range("B1").Interior.ColorIndex = 45
If Range("B1").Value = 1 Then Range("B1").Interior.ColorIndex = 4
End Sub

This code works perfectly for the one cell but I need to do this for a range of cells in different areas of the spreadsheet

I would like the code to work for cell F26 down to F35 to change colour on the basis of the information that is in column C26 down to C35. As this is over 12 months I would like the code to change the colours of the cells I26 down to I35 from the information in F26 down to F35 and so on.

Apologies if this sounds like an extremely stupid question but I am an amateur and just looking for some help from someone who is clued up on this

Upvotes: 0

Views: 152

Answers (4)

Las Ten
Las Ten

Reputation: 1175

OK, then here is the detailed solution:

Column 3 should be hidden, this is just using the sign() formula function: =SIGN(B2-A2) Note that the default background color of the column is the color you want to see for cases where sign=1

this is how the sheet should look like

And these are your conditional formatting rules. I did it for cell B2 only, then filled the formatting for the rest of the column. One rule for sign=1, one for sign=-1 and one for where B2 = 1.

and these are your 3 rules

Upvotes: 1

Trace
Trace

Reputation: 18899

Private Sub Worksheet_Change(ByVal Target As Range)

Dim oCell           As Range

For Each oCell In Target
    Call Input_Ranges(oCell, Range("F26:F35"), -3)
    Call Input_Ranges(oCell, Range("I26:I35"), -3)
    Call Input_Ranges(oCell, Range("L26:L35"), -3)
    Call Input_Ranges(oCell, Range("O26:O35"), -13)
Next oCell

End Sub

Public Sub Input_Ranges(oCell As Range, oRange As Range, iOffset As Integer)

    If Not Intersect(oCell, oRange) Is Nothing Then
        If oCell < oCell.Offset(0, iOffset) Then
             oCell.Interior.ColorIndex = 3
         ElseIf oCell > oCell.Offset(0, iOffset) Then
             oCell.Interior.ColorIndex = 45
         ElseIf oCell = oCell.Offset(0, iOffset) Then
             oCell.Interior.ColorIndex = 6
         ElseIf oCell = 1 Then
             oCell.Interior.ColorIndex = 4
         End If
    End If

End Sub

I have modified the code a second time based on your request (comments).

Small explanation:
The Target is the range that is being changed and triggers the event.
The Intersect method checks if the Target lies within the range defined.
Offset checks the third column left from the Target range that has changed.
You can change this to any column you like.

Upvotes: 0

martin
martin

Reputation: 2638

Do you really need to do it in the Worksheet_Change event? This is going to slow down things a bit.

Anyway, there is no way to write the code like this for a range, you have to iterate through the cells in the range one by one. For example like this:

Dim r As Range
Dim ws As Worksheet
Set ws = ActiveSheet
For Each r In ws.Range("F26:F35")
    If r.Value < ws.Cells(r.Row, 3) Then r.Interior.ColorIndex = 3  '3 -- Column C
    '...
Next

Upvotes: 0

Las Ten
Las Ten

Reputation: 1175

I suggest to use a hidden column with a calculated value (sign of difference, for example) and use a conditional formatting set for that column because Martin is Right, this will slow down your Excel.

However if you really want to do that on that event handler, that routine receives a parameter that is the range that was modified. You can iterate through its rows or columns.

Upvotes: 0

Related Questions