Excel Conditional Formatting macro update results on different sheet.

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

CootaAsked:
Who is Participating?
 
MakriniCommented:
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
 
MakriniCommented:
Try changing

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

for

Private Sub Worksheet_Change(ByVal Target As Range)
0
 
CootaAuthor Commented:
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
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
MakriniCommented:
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
 
CootaAuthor Commented:
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
 
CootaAuthor Commented:
Thanks very much. Worked a treat.
Will bare in mind the workbook size.
Saved me hours of frustration, youre a genius.
0
 
CootaAuthor Commented:
Thanks very much. Worked a treat.
Will bare in mind the workbook size.
Saved me hours of frustration, youre a genius.
0
 
MakriniCommented:
Glad to help - and no genius - just spent way too long listening to guys in forums like this one
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.