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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MakriniCommented:
Try changing

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

for

Private Sub Worksheet_Change(ByVal Target As Range)
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.
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.
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CootaAuthor Commented:
Thanks very much. Worked a treat.
Will bare in mind the workbook size.
Saved me hours of frustration, youre a genius.
CootaAuthor Commented:
Thanks very much. Worked a treat.
Will bare in mind the workbook size.
Saved me hours of frustration, youre a genius.
MakriniCommented:
Glad to help - and no genius - just spent way too long listening to guys in forums like this one
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.