VBA Excel Issue

Question

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

Can you post an example of what is contained in C26 to C35, F26 to F35 and I26 to I35? Hint: Instead of using code, you could use conditional formatting to achieve this. — shahkalpesh
– shahkalpesh, Commented Jul 31, 2012 at 12:01
C26-C35 has varying percentage figures. C26=56% C27=76% C28=83% and so on. The only numbers that will be input into F26 will be between 0% and 100%. I want the sheet to change the cell colour to red if F26 is less than 56% go yellow if its the same as 56% go orange if its an increase and turn green if its 100%. i have tried the conditional formatting but as this only has 3 conditions i am stuck — user1565556
– user1565556, Commented Jul 31, 2012 at 13:05
Take a look at @Kim's solution. I am sure, the code does what you are looking for. — shahkalpesh
– shahkalpesh, Commented Jul 31, 2012 at 13:27

Las Ten · Accepted Answer · 2012-08-03 13:30:01Z

1

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

answered Aug 3, 2012 at 13:30

Las Ten

1,17510 silver badges22 bronze badges

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

Comments

Las Ten · Accepted Answer · 2012-07-31 11:55:48Z

0

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.

answered Jul 31, 2012 at 11:55

Las Ten

1,17510 silver badges22 bronze badges

6 Comments

martin Over a year ago

OP needs four different colors, if I am not mistaken, in Excel 2003 you can have only three rules in conditional formatting. (I haven't seen Excel 2003 for some time.)

Las Ten Over a year ago

Yeah, you're right with Excel 2003 but conditions 1, 2 and 3 can not be true at the same time so a default background and 3 conditionals are fairly enough, AFAIC.

user1565556 Over a year ago

i tried the conditional formatting but as its excel 2003 I am limited to 3 conditions. I appreciate I am behind the times using 2003 but its work related and not personal so I have no say on what excel we use. I think I may be best of using the 3 conditional formats and then just deleting them once I get 100% and manually change it. Probably be more hassle than writing all these codes

Las Ten Over a year ago

But 3 conditional formats are enough if you have one default color for your default status plus 3 conditions ... if you know what I mean.

user1565556 Over a year ago

I understand what you mean but putting it into practice is slightly different for me as I really am an amateur. I didnt realise all the work involved (to me it seems a lot of work but writing the codes are probably second nature to the majority of people who are on here). I wouldnt know how to do what you have advised in all honesty

|

martin · Accepted Answer · 2012-07-31 12:00:29Z

0

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

edited Jul 31, 2012 at 12:00

answered Jul 31, 2012 at 11:53

martin

2,67024 silver badges29 bronze badges

Comments

Trace · Accepted Answer · 2012-07-31 16:04:19Z

0

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.

edited Jul 31, 2012 at 16:04

answered Jul 31, 2012 at 12:24

Trace

18.9k21 gold badges98 silver badges174 bronze badges

11 Comments

user1565556 Over a year ago

This seems to be working fine however when I enter the information the cells still remain orange for 100% rather than change green. Sorry for all the questions

Siddharth Rout Over a year ago

@Kim: You might want to move the For loop inside the If statement?

Trace Over a year ago

I'm sorry you guys, I left a mistake which I have rectified now. I wanted to check each cell to cover the different scenarios (change cell or entire range at once); now it should work as expected.

user1565556 Over a year ago

Hi Kim. This is working now so I would like to thank you for your help. The only problem I have now is to replicate this, so that it runs on various ranges of cells. So I would Like I26:I35 to change colour based on the information in cells F26:F35, then for L26:L35 to read from I26:I35, then O26:O35 to change from L26:L35, C42:C51 to read from O26:O35. I appreciate I am pretty much asking you to do everything but I really am an amateur. Feel free to tell me to Shut up if I am asking too much

user1565556 Over a year ago

I'm not trying to say there is anything wrong with it, its probably something I have done wrong in all honesty and just struggling to work out what I have done. I was just showing my code as an example and I appreciate all your effort as it has gone a long way to helping me understand all this. Sorry for all the hassle and I hopefully will get my head around it eventually

|

Collectives™ on Stack Overflow

VBA Excel Issue

4 Answers 4

Comments

6 Comments

Comments

11 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

6 Comments

Comments

11 Comments

Your Answer

Sign up or log in

Post as a guest

Related