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.


NOTE: This answer was created with Excel 2007, but the same concept can be applied to Excel 2003.

You can do this with Excel's Conditional Formatting feature, and I believe you'll only need two simple rules to make this work.

With your dropdown in A1, bank rankings in C2:H6, and values in I2:N6, you can set up the following rules (using the "Use a formula to determine which cells to format" option):

  • Applied to $C$2:$H$6 -- =$A$1=C2
  • Applied to $I$2:$N$6 -- =$A$1=C2

enter image description here

With these rules, you should get the results you want (e.g., if "Bank 3" is selected in A1, then all values for Bank 3 will be highlighted in your table).

enter image description here

  • Thanks Excellll. Your solution worked in a sense in that the immediate effect was that it applied the correct formatting but when I changed the bank selection in the drop down it did not highlight the values. In excel 2003 the conditional formatting window is a bit different, no "applies to" and no stop if true option. Do I need to alter on change code perhaps? – Kristina Dec 4 '12 at 23:04
  • The following code is used to highlight bank names, it first erases all formats then applies to current bank selected. 'code' Sub HighLightCells2() ActiveSheet.UsedRange.Cells.FormatConditions.Delete ActiveSheet.UsedRange.Cells.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _ Formula1:=Range("A1") ActiveSheet.UsedRange.Cells.FormatConditions(1).Interior.ColorIndex = 4 End Sub' – Kristina Dec 5 '12 at 3:18
  • Need adjustment for Excel 2003, this solution cannot be used – Kristina Dec 5 '12 at 4:08
  • Need adjustment for Excel 2003, current code(earlier code contained error) to highlight cells containing bank names: Sub HighLightCells2() ActiveSheet.UsedRange.Cells.FormatConditions.Delete If Not Range("A1") = vbNullString Then ActiveSheet.UsedRange.Cells.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _ Formula1:=Range("A1") ActiveSheet.UsedRange.Cells.FormatConditions(1).Interior.ColorIndex = 4 End If End Sub – Kristina Dec 5 '12 at 4:19
  • Added the following code but nothing happens ActiveSheet.Range("$J$3:$O$67").FormatConditions.Add Type:=xlExpression, _ Formula1:="=$A$1 = Range(C3)" ActiveSheet.Range("$J$3:$O$67").FormatConditions(2).Interior.ColorIndex = 8 – Kristina Dec 6 '12 at 0:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.