Radio Button to Conditional Format Between Values

[GeeWhiz7]

Once more to the experts...as I'm lost.
Hi folks,

I have an array that changes depending on which radio button is selected.
For example, below, the #1 radio button is selected so the 5-year correlation is shown. If #2 were selected then the 1-year shows up instead.

I would like to have conditional formatting also based on which radio button is selected.
In the example, if radio #1 selected, the array shown is the 5yr correlation and I would like to highlight the values between the threshold #s (-.1 to .1).

I can easily use conditional formatting between the values native to excel, but I cannot figure out how to make it dependent on the radio button selected so that each array can have different thresholds to color the cells.
When I try to record a macro to see how the radio button event is being captured, I don't get anything I can use...i think...

Thanks!
-Will

Sub ButtonCondTest()
'
' ButtonCondTest Macro

    Range("A2:D4").Select

    Application.CutCopyMode = False

'Threshold Values
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
        Formula1:="=$I$4", Formula2:="=$J$4"

'Is this the radio button?  How to allow for 2 radio buttons each with independent formatting when selected?
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority

'Formatting
    With Selection.FormatConditions(1).Font
        .Color = -16383844
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13551615
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
End Sub

 

[JamesCanale]

You can tie the option buttons to a cell (B2 in this case) and then use that in a conditional formatting formula.

MrExcelPlayground5.xlsx
	A	B	C	D	E	F	G	H	I	J
1
2		2			-0.1	0.1		0.51	0.94	-0.18
3					-0.2	0.2		-0.30	-0.86	-0.82
4								0.16	-0.48	0.83
5								0.95	0.28	-0.13
6								-0.43	0.50	0.04
7								-0.37	0.02	-0.59
8								0.14	-0.35	-0.44
9								0.59	-0.01	0.77
10								-0.66	0.52	-0.05
11								0.56	0.10	-0.13
Sheet25

Cell Formulas
Range	Formula
H2:J11	H2	=RANDARRAY(10,3,-1,1,FALSE)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H2:J11	Expression	=IF($B$2=1,IF(AND(H2<=$F$2,H2>=$E$2),TRUE,FALSE),IF($B$2=2,IF(AND(H2<=$F$3,H2>=$E$3),TRUE,FALSE),FALSE))	text	NO

I see now that the option buttons aren't appearing in the minisheet. Take my word on it - there are two option buttons, and they are tie to B2.

 

[GeeWhiz7]

You can tie the option buttons to a cell (B2 in this case) and then use that in a conditional formatting formula.

MrExcelPlayground5.xlsx
	A	B	C	D	E	F	G	H	I	J
1
2		2			-0.1	0.1		0.51	0.94	-0.18
3					-0.2	0.2		-0.30	-0.86	-0.82
4								0.16	-0.48	0.83
5								0.95	0.28	-0.13
6								-0.43	0.50	0.04
7								-0.37	0.02	-0.59
8								0.14	-0.35	-0.44
9								0.59	-0.01	0.77
10								-0.66	0.52	-0.05
11								0.56	0.10	-0.13
Sheet25

Cell Formulas
Range	Formula
H2:J11	H2	=RANDARRAY(10,3,-1,1,FALSE)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H2:J11	Expression	=IF($B$2=1,IF(AND(H2<=$F$2,H2>=$E$2),TRUE,FALSE),IF($B$2=2,IF(AND(H2<=$F$3,H2>=$E$3),TRUE,FALSE),FALSE))	text	NO

I see now that the option buttons aren't appearing in the minisheet. Take my word on it - there are two option buttons, and they are tie to B2.

Hi James,
I'm realizing I missed a key point in my original post. The array data (H2:J11 in your example) already has a formula in them to get the correct array from another sheet so I can't put the conditional formatting formula in there. The native excel conditioning doesn't conflict with this for some reason, but trying to put a conditional formula will. All the arrays have formulas unfortunately, even in the other sheets.

 

[JamesCanale]

I don't understand why you can't have a conditional formatting formula and a content formula for the same cell. My example has a formula in the array and a separate conditional formatting formula.

 

[GeeWhiz7]

I don't understand why you can't have a conditional formatting formula and a content formula for the same cell. My example has a formula in the array and a separate conditional formatting formula.

Sorry, forgot about where the conditional formula gets entered (under cond. formatting, not in the cell). This works. thank you James.