Solved

Excel vba - added columns and now formatting isn't working.

Posted on 2009-06-29
13
269 Views
Last Modified: 2013-11-25
I have a workbook that has conditional color formatting based on the data input in the cell. Everything was working great until I added a couple of columns. Now no color formatting is appearing at all. I believe I have adjusted all of the code, but apparently I've messed something up. Also, on my worksheets labeled "RCBM" and "MZ" I'm getting "REF#" instead of a value. Any ideas? Thanks!
09-10-Bmrks-1st-gr.xls
0
Comment
Question by:McQMom
  • 6
  • 4
  • 2
13 Comments
 
LVL 18

Accepted Solution

by:
WarCrimes earned 250 total points
ID: 24740243
Well the #Ref is an easy fix.  Your named range 'Name' is only defined out to Column AP on sheet Class Data.  It needs to be out to Column AR.
0
 
LVL 18

Expert Comment

by:WarCrimes
ID: 24740261
As for the conditional formatting, in which cells do you have the formulas set up that aren't working?  I don't see any conditional formatting where I assumed it would be.

WC
0
 
LVL 3

Expert Comment

by:Devom
ID: 24740284
The #Ref errors are because you adjusted the vlookups by two columns but didn't extend the table you were referencing.  To fix this, change Name to reference from A2 to AR rather than AP (go to the insert menu, define, click name change the reference and click add).

The conditional formatting seems to be working for me, where do you see it failing?
0
 

Author Comment

by:McQMom
ID: 24740290
All of the columns except for A (Student name) & B (Student id) should be coloring based on the scores put in. For example, scores put in under MN (column C I believe) should color red if the score is 0-1, orange if 2-5, yellow if 6-9, green if 10-13, blue if 14-17, or purple if 18+. The same color formatting should appear on Column B in the worksheet named "MN". Thanks for the Ref# info. I'll make that change right away. I knew I must have been missing something very simple.
0
 
LVL 3

Assisted Solution

by:Devom
Devom earned 250 total points
ID: 24740459
The conditional formatting seems to be working just maybe not everywhere you want it to.  The macro is looking for changes in cells starting at row 4 so values changed in row 3 won't be affected.  Either insert a row on top of each sheet or go into the macro and replace all 4: with 3:
0
 
LVL 18

Expert Comment

by:WarCrimes
ID: 24740651
I noticed the same thing and was working on making your code more efficient.

Your formatting code has a lot of unneccessary IF statements.  The Target cell can only be in one column at a time, so just use a Select statement on it's column letter.

Instead of worrying about replacing 4 with 3, just match against the column and you won't have to worry how many students are in the range in the future, as long as the Intersect check in the Worksheet_Change event passes it's test then the following code should work like a charm, even after further updates to the rest of your workbook.

You should copy my logic below for the rest of your Formatting macros instead of using multiple IF statements.  Each time the code enters those macros, each and every one of those IF conditions is checked.  Even using a more efficient IF-ELSEIF statement, it would check all conditions until it found a matching one.  Using the Case statement is much more efficient, and a heck of a lot easier to read.

Cheers,
WC

P.S.
I wouldn't have minded a split here seeing as I answered your #Ref Question.
'These two subs should go on a regular module sheet

Sub BenchmarksFormatting(rg As Range)

Dim Target As Range

Dim FillColors As Variant, v As Variant

FillColors = Array(38, 40, 36, 35, 37, 39)

With rg.Worksheet

    For Each Target In rg.Cells

        If Target <> "" And IsNumeric(Target) Then

            'If Not Intersect(Target, .Range("B4:B153")) Is Nothing Then 'name

                'v = Application.Match(Target, Array(0, 5, 7, 10, 14, 19), 1)

                'If Not IsError(v) Then

                    'Target.Interior.ColorIndex = FillColors(v - 1)

                'End If

            'End If

            Select Case Left(Target.Address(1, 0), InStr(1, Target.Address(1, 0), "$") - 1)

                Case "C" 'MN Fall

                    v = Application.Match(Target, Array(0, 2, 6, 10, 14, 18), 1)

                Case "D" 'QD Fall

                    v = Application.Match(Target, Array(0, 4, 12, 21, 27, 32), 1)

                Case "E" 'NI Fall

                    v = Application.Match(Target, Array(0, 15, 27, 41, 52, 61), 1)

                Case "F" 'OC Fall

                    v = Application.Match(Target, Array(0, 39, 56, 69, 79, 89), 1)

                Case "G" 'MA Fall

                    v = Application.Match(Target, Array(-1, 0, 1, 4, 8, 11), 1)

                Case "U" 'LNF Fall

                    v = Application.Match(Target, Array(0, 20, 31, 42, 54, 64), 1)

                Case "V" 'LSF Fall

                    v = Application.Match(Target, Array(0, 9, 18, 28, 38, 47), 1)

                Case "W" 'PSF Fall

                    v = Application.Match(Target, Array(0, 8, 23, 36, 47, 56), 1)

                Case "X" 'NWF Fall

                    v = Application.Match(Target, Array(0, 7, 16, 27, 41, 58), 1)

                Case "Y" 'TWW Fall

                    v = Application.Match(Target, Array(0, 1, 3, 6, 10, 15), 1)

                Case "Z" 'CWS Fall

                    v = Application.Match(Target, Array(-2, -1, 0, 1, 4, 7), 1)

                Case "AA" 'RCBM Fall

                    v = Application.Match(Target, Array(-1, 0, 4, 11, 27, 60), 1)

                Case "AB" 'MZ Fall

                    v = Application.Match(Target, Array(-2, -1, 0, 1, 3, 7), 1)

                Case "H" 'MN Winter

                    v = Application.Match(Target, Array(0, 8, 11, 15, 18, 19), 1)

                Case "I" 'QD Winter

                    v = Application.Match(Target, Array(0, 15, 23, 30, 34, 37), 1)

                Case "J" 'NI Winter

                    v = Application.Match(Target, Array(0, 34, 47, 57, 67, 74), 1)

                Case "K" 'OC Winter

                    v = Application.Match(Target, Array(0, 55, 67, 78, 88, 94), 1)

                Case "L" 'MA Winter

                    v = Application.Match(Target, Array(0, 4, 7, 11, 16, 22), 1)

                Case "AC" 'LNF Winter

                    v = Application.Match(Target, Array(0, 25, 40, 53, 65, 75), 1)

                Case "AD" 'LSF Winter

                    v = Application.Match(Target, Array(0, 18, 29, 40, 50, 60), 1)

                Case "AE" 'PSF Winter

                    v = Application.Match(Target, Array(0, 26, 38, 48, 57, 66), 1)

                Case "AF" 'NWF Winter

                    v = Application.Match(Target, Array(0, 24, 35, 48, 66, 91), 1)

                Case "AG" 'TWW Winter

                    v = Application.Match(Target, Array(0, 4, 7, 12, 17, 24), 1)

                Case "AH" 'CWS Winter

                    v = Application.Match(Target, Array(-1, 0, 2, 4, 9, 14), 1)

                Case "AI" 'RCBM Winter

                    v = Application.Match(Target, Array(0, 8, 15, 28, 56, 89), 1)

                Case "AJ" 'MZ Winter

                    v = Application.Match(Target, Array(-1, 0, 1, 3, 8, 13), 1)

                Case "M" 'MN Spring

                    v = Application.Match(Target, Array(0, 9, 12, 16, 18, 20), 1)

                Case "N" 'QD Spring

                    v = Application.Match(Target, Array(0, 20, 26, 31, 36, 38), 1)

                Case "O" 'NI Spring

                    v = Application.Match(Target, Array(0, 40, 51, 61, 69, 76), 1)

                Case "P" 'OC Spring

                    v = Application.Match(Target, Array(0, 62, 73, 83, 90, 96), 1)

                Case "Q" 'MA Spring

                    v = Application.Match(Target, Array(0, 6, 10, 15, 21, 28), 1)

                Case "AK" 'LNF Spring

                    v = Application.Match(Target, Array(0, 31, 46, 59, 70, 81), 1)

                Case "AL" 'LSF Spring

                    v = Application.Match(Target, Array(0, 23, 33, 44, 55, 65), 1)

                Case "AM" 'PSF Spring

                    v = Application.Match(Target, Array(0, 35, 44, 53, 62, 71), 1)

                Case "AN" 'NWF Spring

                    v = Application.Match(Target, Array(0, 32, 44, 62, 86, 117), 1)

                Case "AO" 'TWW Spring

                    v = Application.Match(Target, Array(0, 8, 12, 18, 25, 32), 1)

                Case "AP" 'CWS Spring

                    v = Application.Match(Target, Array(0, 2, 4, 9, 15, 21), 1)

                Case "AQ" 'RCBM Spring

                    v = Application.Match(Target, Array(0, 18, 34, 59, 88, 116), 1)

                Case "AR" 'MZ Spring

                    v = Application.Match(Target, Array(0, 1, 4, 8, 13, 18), 1)

                End Select

                

                If Not IsError(v) Then

                    Target.Interior.ColorIndex = FillColors(v - 1)

                End If

        End If

    Next

End With

End Sub

Open in new window

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:McQMom
ID: 24741407
SO sorry! I didn't pay attention that it was from 2 different people! I was in stress mode. Is there a way I can fix that?
0
 
LVL 18

Expert Comment

by:WarCrimes
ID: 24741982
Yes, I believe you can by clicking on the Request Attention link in your original question.  It's in the bottom right of that comment.  Then just ask them to either re-open so you can assign points, or let them know how you'd like to assign points and they can probably take care of it for you.

Did you try out the code above?  It might be useful to avoid this again if you change the sheet to include more header rows or more rows of students.

WC
0
 

Author Comment

by:McQMom
ID: 24742025
Just "requested attention" so hopefully that will take care of it. I haven't had a chance to plug in the code yet. They've got me making a bunch of other changes that will keep me up until midnight. I'll let you know though. This looks MUCH better!
0
 
LVL 18

Expert Comment

by:WarCrimes
ID: 24742044
I know the feeling.  I'm updating a report writing tool for our analytics group write now.  It's a mess fixing someone else's code sometimes.  It's always easier when their code is efficient and easy to understand.  Hopefully the example I provided will make it easier to make adjustments in the future for you.

Cheers,
WC
0
 

Author Closing Comment

by:McQMom
ID: 31598124
Good grief! I must be blind! Thank you SO much! I was going crazy!!!!!
0
 
LVL 18

Expert Comment

by:WarCrimes
ID: 24766975
Also,

I was mistaken before.

Select Case statements are pretty much If-ElseIf statements, as they will check each item until a match is found, then exit the Select statement.

Thus if you have Case(s) that are more common, you should put them at the top of the Select Statement so the conditional check exits faster.

Cheers,
WC
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now