vba code conditional formatting in excel 2003

[kpicciot]

I have 2 tables consisting of six quarters of data, the first table contains bank names and the second adjacent table contains the values associated with each bank. Each quarter of data is sorted in ascending order according to corresponding values in values table.

I have a drop down box that allows user to select a bank which in turn highlights all occurrences of that bank name in the table. I need the corresponding values to also be highlighted as well. This could easily be accomplished using conditional formatting feature in later versions of excel but excel 2003 does not provide necessary options(applies to option, for example). The dropdown is in <CODE>A1</CODE>, bank rankings in <CODE>C3:O17</CODE>, and values in J<CODE>3:O17

</CODE>The code below removes highlight from previously selected bank and calls code that highlights currently selected bank from drop down.


Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Target.Worksheet.Range("A1")) Is Nothing Then HighLightCells2
End Sub

This code highlights currently selected bank from drop down.

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

I think a formula =$A$1=C3 is necessary to apply highlight to value cells but I am not certain of syntax and whether(or how) it should be added in the above code as a format condition. I can provide any further details necessary.

 

[p45cal]

I think this is perfectly possible in Excel 2003 if you use the Formula Is option. Could you supply a workbook on the interweb somewhere and link to it here? Otherwise, perhaps a screenshot or use Excel Jeanie Html or Mr Excel Html maker, both linked-to in this sticky:http://www.mrexcel.com/forum/excel-questions/628649-recommended-add-ins-links.html

 

[kpicciot]

Thanks p45cal


Through significant trial and error I was finally able to get it to work. I did use VBA however, the Formula Is option didn't work in A1 and when I applied it to the range it worked once but would change relative references the moment I changed selection in A1 drop down. Perhaps, I'm doing it incorrectly but I gave up and worked with the code...which required I activate the first cell of the valued range before using the formula. Take a look. Maybe there is a more direct way of accomplishing it...altho this works. (fyi: Name ranges were in C3:H17)
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
ActiveSheet.Range("$J$3:$O$178").Cells.Activate
ActiveSheet.Range("$J$3:$O$178").Cells.FormatConditions.Add Type:=xlExpression, _
Formula1:="=$A$1 = c3"
ActiveSheet.Range("$J$3:$O$178").Cells.FormatConditions(2).Interior.ColorIndex = 4
ActiveSheet.Range("a1").Cells.Activate

End If
End Sub