How do I format the font colour of an Excel cell with the colour of its sheet tab in VBA ?

I have an Excel 2010 application. I want to test the contents of a cell in VBA and if it meets that condition to change the colour of the text to the same colour of the sheet tab.

How do I do this in VBA code ?

Who is Participating?

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

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.

[ fanpages ]IT Services ConsultantCommented:
Hi Toco,

Here is an example where selecting cell [C10] ("the condition") sets the (fore)colo(u)r of the contents of that cell to the same colo(u)r as the "Tab" for that worksheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

  If Target.Address = [C10].Address Then
     Target.Font.Color = ActiveSheet.Tab.Color
     Target.Font.TintAndShade = ActiveSheet.Tab.TintAndShade
  End If
End Sub

Open in new window

Can you use that code as a starting point to add your own condition for the appropriate cell?

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
Roy CoxGroup Finance ManagerCommented:
Try this

ActiveCell.Font.ColorIndex = ActiveSheet.Tab.ColorIndex

Open in new window

TocogroupAuthor Commented:
Thanks both for your solutions but I've gone for the first I received.
It works great.
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 Excel

From novice to tech pro — start learning today.