Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Excel Conditional Formatting macro update results on different sheet.

Posted on 2010-01-05
8
Medium Priority
?
271 Views
Last Modified: 2012-08-14
I have multiple 2003 workbooks that use CF (Conditional Formatting) on multiple protected sheets, that also link back to 1 summary sheet that also has CF setup to reflect the background colour status using =Sheet!cell formula. This works fine.
I need to add an extra condition, due to 2003 limitations of 3. I have hijacked and modified some code to include the extra colour condition, no problems on the individual sheets apart from now needing to allow cell formatting for the macro to run on the protected sheet  not happy.
My problem is that on my summary sheet, the status colour will only change state when the particular linked cell is clicked with the mouse.
I have tried to use Worksheet.Activate and ScreenUpdating = True with no joy.
Is this possible to do?
Why will CF update and also run on protected sheets?
I have added the code I am using for CF, the Range changes on different sheets, but this works ok.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim icolor As Integer

        If Not Intersect(Target, Range("H13:I44")) Is Nothing Then

                Select Case Target

                        Case "r"
                                 icolor = 3 'RED
                                
                        Case "R"
                                 icolor = 3 'RED
                                 
                        Case "y"
                                icolor = 44 'GOLD
                                 
                        Case "Y"
                                icolor = 44 'GOLD
                                
                        Case "g"
                                icolor = 43 'LIME
                                
                        Case "G"
                                icolor = 43 'LIME
                                
                        Case "c"
                                icolor = 41 'LIGHT BLUE
                                
                        Case "C"
                                icolor = 41 'LIGHT BLUE

                         Case Else

                                'Whatever
                               
                End Select

                

                Target.Interior.ColorIndex = icolor
                Target.Font.ColorIndex = icolor
                
        

        End If
End Sub

Open in new window

0
Comment
Question by:Coota
  • 4
  • 4
8 Comments
 
LVL 10

Expert Comment

by:Makrini
ID: 26187883
Try changing

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

for

Private Sub Worksheet_Change(ByVal Target As Range)
0
 

Author Comment

by:Coota
ID: 26195396
Thanks Makrini,

Making this change on my summary sheet made no difference.
But when I made the change to a linked sheet this gave the same problem as my summary sheet.
I would make the selection ie: r,y,g or c - and then need to click the cell for the background color to change.
What I forgot to mention- if I remove the link on my summary page and select r,y,g or c the formatting works fine. It is as if the CF macro can not see the linked character untill a enter function is performed on the summary cell.
0
 
LVL 10

Expert Comment

by:Makrini
ID: 26195487
Ahhh - ok - sorry I see the question properly now...

  These events will only fire when you actually change the cells themselves.  When they change through a formula - these events do not operate.

  The only way I know around this is to run the Macro on all cells within the
 Worksheet_Calculate()

event.   Unfortunately you don't have the option within this event to know which cell changed so you will have to loop through every cell that you want to check - every time you calculate.

  - or you can run it when the linked cell value changes only.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:Coota
ID: 26206606
I have tried all sorts of combinations without any luck, my knowledge is not great when it comes too VBA. Can you look at my mocked up file to see if it is possible.
I removed all my attempts and changes to keep it clean.
I think unless I move to 2007 I may be asking too much.
CFupdate.xls
0
 
LVL 10

Accepted Solution

by:
Makrini earned 2000 total points
ID: 26207454
Try this.

  All I have done is taken your existing routine and turned it into a function.  I have then used that function on each cell that has value in the summary sheet - and attached it to a sheet activate event.  That way whenever you select that sheet it will recalculate all the colours.

   If your workbook became very large this would not be an ideal solution as it loops through cells every time you activate...  - but in most cases it should do what you want with delays that are too small to be noticed.
CFupdate.xls
0
 

Author Comment

by:Coota
ID: 26279737
Thanks very much. Worked a treat.
Will bare in mind the workbook size.
Saved me hours of frustration, youre a genius.
0
 

Author Closing Comment

by:Coota
ID: 31673294
Thanks very much. Worked a treat.
Will bare in mind the workbook size.
Saved me hours of frustration, youre a genius.
0
 
LVL 10

Expert Comment

by:Makrini
ID: 26281643
Glad to help - and no genius - just spent way too long listening to guys in forums like this one
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

810 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