PDA

View Full Version : Conditional Formatting cells containing certain (amongst other) values



zest1
06-14-2006, 02:13 PM
Can some please tell me what formula to use for conditional formatting cells that contain a certain value (regardless of other possible values in those cells) as referenced from another cell?

Example:
A2 = 100
D2 = 70,90,100,120
E2 = 40,80
F2 = 20,60,100

I’d like to use the value reference in A2 to conditionally format all other cells that contain that value (100), regardless of the other values in the cells

Thus, D2 & E2 are CF'd

Thanks a lot

mdmackillop
06-14-2006, 03:20 PM
Maybe not with conditional formatting (but happy to be proved wrong!)
Try

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If InStr(1, Target, ",") = 0 Then Exit Sub
If Target = [A2] Then
Target.Interior.ColorIndex = 6
Exit Sub
End If
For i = 0 To UBound(Split(Target, ","))
If --Split(Target, ",")(i) = [A2] Then Target.Interior.ColorIndex = 6
Next
End Sub


I don't believe this is very robust because of the way commas are used in numbers. 100,200 is a number, and "," will not be found. 20,100,20 will be seen as text, and the code should work.
A sample of your file might help.
Regards
MD

zest1
06-14-2006, 04:08 PM
thanks MD.

I attached a sample for you, to better explain what I'd like to do.

mdmackillop
06-15-2006, 12:32 AM
Hi Zest,
I don't really follow what is meant to format what in your sample. Can you add some notes/pointers on your sample to explain in detail.
Also, your samples do not contain any cells which could be construed as numeric. Is this guaranteed in a "real life" situation?, or can all the "number" cells on sheet 2 be formatted as text?
Regards,
MD

Hotpepper
06-16-2006, 12:04 AM
This can be done with Conditional Formatting.

But your sample is not exactly clear. It looks like for each date that if any of those numbers appear on the line on Sheet 2 it should be conditionally formatted, but for 2/2/06 you have 2/2/2006 30 40 70

but only 2 cells shaded yellow even though 30 appears in the other 2 cells you don't have them highlighted. What are you trying to do exactly?

mdmackillop
06-16-2006, 12:40 AM
Hi HotPepper,
Welcome to VBAX. Great to see a new member jumping in with a solution, and I look forward to seeing your CF,
Regards
MD