5 Conditional formatting

Humar June 4, 2010 at 07:24:03

Hi, If the color of the cell is dependent on a formula, identify which cell or cells are precedents of the cell with the formula. Make your macro respond to changes in those precedent cells. For example if the formula in cell X1 is =A1*B2 then you test the change event with: If Target.Address = "$A$1" Or Target.Address = "$B$2" Then (Note the $ signs in the address string) Then the macro uses the calculated value in X1 to select the color - using the Select Case Select Case ActiveSheet.Range("X1").Value Regards

Thanks very much for your help, the only problem is the above code only works in a worksheet change (view code) and not in a macro module. so the cell only changes when I change a number and I have tried to assign it to a macro without success. Please advise. If i had a button that could do this in macro will be great, or even automatically.

Humar June 4, 2010 at 08:08:14

Hi, Am I right in thinking that you want the color of a cell to change dependent on it's value, and that the cell contains a formula. If so then the formula must be dependent on other cells. The change event triggers when any cell on the worksheet is changed, so just set the on change event to respond to only those cells that cause your formula to change value. Please post the formula and the address of the cell it is in. Regards

=(F48+F47+F46)/3 so if result is between 10 and 25 red if > 25 blue if between -10 and -25 yellow if > -25 green Work sheet code is below but only works if I type it in again. Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer If Not Intersect(Target, Range("G48:G65")) Is Nothing Then Select Case Target Case 10 To 25 icolor = 6 Case Is > 25 icolor = 3 Case -10 To -25 icolor = 11 Case Is < -25 icolor = 9 Case Else 'Whatever End Select Target.Interior.ColorIndex = icolor End If End Sub Thanks

Humar June 4, 2010 at 13:05:35

Hi, You didn't say which cell contained your formula. I have used cell G4 (G4 contains =(F48+F47+F46)/3). As a result you will have to change G4 in the code (it appears twice in the code and 3 other times in comments). Select the name tab of the worksheet containing cell G4 and cells F46:F48. In the visual basic window that opens enter this code: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) 'stop changes made by this code from re-triggering it Application.EnableEvents = False On Error GoTo ErrHnd Dim rngIsect As Range Dim intColor As Integer 'respond to changes in cells F46, F47 & F48 only Set rngIsect = Intersect(Target, ActiveSheet.Range("F46:F48")) 'test if changed cell is in the required range If Not rngIsect Is Nothing Then 'select color based on value in cell G4 'change cell as required 'G4 contains =(F48+F47+F46)/3 Select Case ActiveSheet.Range("G4").Value Case 10 To 25 intColor = 6 Case Is > 25 intColor = 3 Case -10 To -25 intColor = 11 Case Is < -25 intColor = 9 Case Else intColor = xlColorIndexNone End Select 'change cell G4's color ActiveSheet.Range("G4").Interior.ColorIndex = intColor End If 're-enable events Application.EnableEvents = True Exit Sub 'error handler ErrHnd: Err.Clear 're-enable events Application.EnableEvents = True End Sub Any changes to cells F46, F47 or F48 will result in the color of cell G4 changing based on the value in that cell resulting from the formula =(F48+F47+F46)/3 (I didn't test the logic of your color selections) Regards

Hi, thanks for all your help I really appreciate it. The issue is I have about 15 columns and 18 rows of data that need to change color. I have a total of 80 columns and every 4 will be doing the color code. so I will need to apply the code to all the columns. the code cant be specific. Can it be more flexible please? Sample Male Female difference 10 15 15 25 28.33333333 10 45 25 2 5 16.66666667 1.666666667 the difference column will be the calculated field changing colors.

Humar June 5, 2010 at 13:25:34

Reply, 1. How does the formula you posted (=(F48+F47+F46)/3)) relate to the difference column will be the calculated field changing colors that you refer to in response 6. 2. The code can be made to respond to any change event on the worksheet - i.e. it will be very general. But the code still has to know specifically which cells have to be tested for values and have their colors updated each time. Can you be specific about which cells will have color applied based on their values. 3. You say The issue is I have about 15 columns and 18 rows of data that need to change color. Do you really mean that you have 270 cells that change color (15 * 18) 4. I don't understand your 'sample' If you post data between the <pre> and </pre> tags that you can find above the reply box it would help, as the data can be lined up. Including column and row letters/numbers also helps. Regards

Ok, 1) The formula is in the difference column which will be where the colors will show. so I have a difference column 15 times. Color changes if the result of the formula is between 10 to 25 >25 -10 to -25 > -25 Columns are A, C, F, i, M, O, Q, U, V, X, AA, AC, AF, AI, AO (cells 48 to 65 for each row) Hope this is clear, dont have access to my system at the moment. Does this help?

Humar June 5, 2010 at 15:22:21

Hi, This macro responds to any change event on the worksheet. It then tests the values in all 270 specified cells and changes their color according to their value. Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) 'stop changes made by this code from re-triggering it Application.EnableEvents = False On Error GoTo ErrHnd Dim rngCell As Range Dim intC As Integer Dim intColor As Integer 'respond to changes in any cell on this worksheet 'test values in 270 cells in columns: 'A, C, F, I, M, O, Q, U, V, X, AA, AC, AF, AI & AO 'and rows 48 to 65 'loop through all cells in range A48 to AO65 For Each rngCell In ActiveSheet.Range("A48:AO65") intC = rngCell.Column 'test if cell is in one of the specified columns If intC = 1 Or intC = 3 Or intC = 6 Or intC = 9 _ Or intC = 13 Or intC = 15 Or intC = 17 Or intC = 21 _ Or intC = 22 Or intC = 24 Or intC = 27 Or intC = 29 _ Or intC = 32 Or intC = 35 Or intC = 41 Then 'if so - test cell value Select Case rngCell.Value Case 10 To 25 intColor = 6 Case Is > 25 intColor = 3 Case -10 To -25 intColor = 11 Case Is < -25 intColor = 9 Case Else intColor = xlColorIndexNone End Select 'and change cell's color rngCell.Interior.ColorIndex = intColor End If Next rngCell 're-enable events Application.EnableEvents = True Exit Sub 'error handler ErrHnd: Err.Clear 're-enable events Application.EnableEvents = True End Sub Regards

Okay, thanks very much for your help. Really really appreciate!!!!! Thanks

Hi, just tried the code on my worksheet and nothing is happening. I have pasted it into the view code and also a module and its not chnaging color. Please advise.

Humar June 7, 2010 at 09:15:27

Hi, The macro is a change event macro, meaning that it runs when a change occurs in a cell on the worksheet it is attached to. Nothing will ever happen if it is in a Module. Select the worksheet containing the 270 values. Right-click on the name tab at the bottom and select 'View code' In the Visual basic window that opens make sure that the window is empty - delete any code present. Then copy the whole of the code I submitted, from Option Explicit to End Sub (inclusive) and paste it into the Visual Basic window. Use Alt+f11 to go back to the main Excel window. Click on any empty cell in the worksheet and enter something and click enter. The 270 cells should change color. If nothing happens, it is possible that Events are disabled. In The Visual Basic window, from the Menu bar, select 'View' and 'Immediate Window'. In the Immediate window that opens, scroll down to the bottom of the list and enter: Application.EnableEvents = True and click Enter, and test again. If it still does nothing, add a Break Point to the code (at the For Each rngCell ... line) and see if it stops at that point. Regards