I have a drop down in Excel to allow user to select a comparison bank. When a bank is selected for comparison all occurrences of that bank are highlighted in the tables below the drop down. Here is my question: I would also like to highlight the associated values in the table adjacent to the table containing the bank names. The associated values are in the same row but six columns to the right. I used the following code to run on change
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Target.Worksheet.Range("A1")) Is Nothing Then HighLightCells2
End Sub
which works perfectly, highlighting whichever bank name is selected.
I need some help with code that will also highlight the values in matching quarter for the associated bank. I am not able to post an image-- here's a description. drop down with bank names, 6 columns of data(for 6 quarters) containing ranked bank names. Adjacent are 6 columns with amounts that relate to columns with bank names. Bank ranking changes across quarters so while the list of banks is constant the order changes. The value columns to the right are in the same order as the banks so if "Super Bank" is listed in third row of column C(4q2008)
the associated 4q2008 value for Super Bank is found in column J
, and that is the value I want highlighted. There are 6 qtrs of data, so when a bank is selected from the dropdown and all occurrences highlighted in the bank name table, I would also like the all associated values for the same 6 qtrs to be highlighted in the value table.
I don't know VBA very well. I think I can use offset... but identifying the range and or cell to apply format to is not clear to me. Any suggestions appreciated.