200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Dear,
I have list of products mentioned in Column D. BAsed on the product, i want to highlight multiple cells in that row with a cell colour.
For Eg: If Product name is A, then highlight Cell B2, S2 and K2.
If Product name is B, then highlight Cell B2, E2 and M2 and so on.
Like this i have 50 products and i need to highlight multiple cells in that row.
Please help me using conditional formatting or VBA coding. Also i need to check this for more than 30 worksheet in my file.
You need to add a sheet to your workbook on which to keep the instructions about which columns should be highlighted for each of your 50 products. In the attached workbook that is the sheet "Products". You can use another name, but must implement that change in the code as well (I have marked the spot).
That sheet has a named range "Products" (you can change the name) which the code needs. I strongly suggest that you also use it to do data validation in your worksheets. Samples are in the worksheet "SampleSheet". However, this isn't a requirement. Only, if a product appears in the sheet which is not listed in "Products" there will be no instructions which columns to highlight.
This is the code you need to have in the codesheet of every sheet on which you wish action, all 30 of them. You can copy and paste.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
' 13 Sep 2017
On_Change Target
End Sub
The tab "SampleSheet" is a sample for you to test. Its name is immaterial. Note that the code expects the columns which are highlighted to have data in them. If there are blank cells removal of previous highlights will not work as expected. Other code could be found in that case once the construction of your worksheets is known.
All the code doing the actual work is in the module 'Highlights'. It is required only once. The same code works for all 30 worksheets. Please bear this in mind. If the columns aren't always the same for all sheets this approach will not be possible.