CF in non contiguous range of columns

[hsandeep]

I want to do CF in 11 cells from V94:AJ94. This range V94:AJ94 is non-contiguous columns. The cell addresses are: V94, W94, X94, Y94, AA94, AB94, AC94, AG94, AH94, AI94, AJ94

Also, a cell B94 has formula which generates value either of 1 to 11 or zero or null

CF criteria’s:
If B94=1 to 11, then only the nth position of the cell in V94:AJ94, to be considered left to right, to have CF rules to give output as green background & all other cells CF rules to give output as red background.

If B94=0 or null, then all cells in V94:AJ94 to have CF rules to give output as red background

Formula requested for CF rules manager.

How to accomplish please?

 

[AlphaFrog]

This macro will set the CF rule for green and set the default interior color red. Run it once.

Sub SetCF()
    With Range("V94, W94, X94, Y94, AA94, AB94, AC94, AG94, AH94, AI94, AJ94")
        Range("V94").Select
        .Interior.Color = vbRed
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlExpression, Formula1:="=AND($B94<>"""",$B94>=CHOOSE(COLUMN()-21,1,2,3,4,,5,6,7,,,,8,9,10,11))"
        .FormatConditions(1).Interior.ColorIndex = 4
    End With
End Sub

 

[hsandeep]

Can I get a non vba solution please

 

[AlphaFrog]

The code just sets it up. Run it once and delete the code.

Or Put this CF formula in each cell for green
=AND($B94<>"",$B94>=CHOOSE(COLUMN()-21,1,2,3,4,,5,6,7,,,,8,9,10,11))

Set the default interior color red for each cell

 

[hsandeep]

)

I pasted your formula in the CF Rule in all the 11 cells from V94:AJ94. When B94=3, then only X94 (3rd position) should have green color background. But the first 3 cells from V94:AJ94 i.e. V94, W94 & X94 are giving output as green color background.

 

[hsandeep]

The code just sets it up. Run it once and delete the code.

Or Put this CF formula in each cell for green
=AND($B94<>"",$B94>=CHOOSE(COLUMN()-21,1,2,3,4,,5,6,7,,,,8,9,10,11))

Set the default interior color red for each cell

Screenshot:

B

C

G

H

I

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

AG

AH

AI

AJ

94

3

95

TRUE

TRUE

TRUE

FALSE

FALSE

FALSE

FALSE

FALSE

FALSE

FALSE

FALSE

Spreadsheet Formulas

Cell Formula V95 =AND($B94<>"",$B94>=CHOOSE(COLUMN()-21,1,2,3,4,,5,6,7,,,,8,9,10,11)) W95 =AND($B94<>"",$B94>=CHOOSE(COLUMN()-21,1,2,3,4,,5,6,7,,,,8,9,10,11)) X95 =AND($B94<>"",$B94>=CHOOSE(COLUMN()-21,1,2,3,4,,5,6,7,,,,8,9,10,11)) Y95 =AND($B94<>"",$B94>=CHOOSE(COLUMN()-21,1,2,3,4,,5,6,7,,,,8,9,10,11)) AA95 =AND($B94<>"",$B94>=CHOOSE(COLUMN()-21,1,2,3,4,,5,6,7,,,,8,9,10,11)) AB95 =AND($B94<>"",$B94>=CHOOSE(COLUMN()-21,1,2,3,4,,5,6,7,,,,8,9,10,11)) AC95 =AND($B94<>"",$B94>=CHOOSE(COLUMN()-21,1,2,3,4,,5,6,7,,,,8,9,10,11)) AG95 =AND($B94<>"",$B94>=CHOOSE(COLUMN()-21,1,2,3,4,,5,6,7,,,,8,9,10,11)) AH95 =AND($B94<>"",$B94>=CHOOSE(COLUMN()-21,1,2,3,4,,5,6,7,,,,8,9,10,11)) AI95 =AND($B94<>"",$B94>=CHOOSE(COLUMN()-21,1,2,3,4,,5,6,7,,,,8,9,10,11)) AJ95 =AND($B94<>"",$B94>=CHOOSE(COLUMN()-21,1,2,3,4,,5,6,7,,,,8,9,10,11))

 

[AlphaFrog]

I pasted your formula in the CF Rule in all the 11 cells from V94:AJ94. When B94=3, then only X94 (3rd position) should have green color background. But the first 3 cells from V94:AJ94 i.e. V94, W94 & X94 are giving output as green color background.

Sorry. That's what I thought you wanted.

Change it to this...
=$B94=CHOOSE(COLUMN()-21,1,2,3,4,,5,6,7,,,,8,9,10,11)

 

[hsandeep]

Change it to this...
=$B94=CHOOSE(COLUMN()-21,1,2,3,4,,5,6,7,,,,8,9,10,11)

Sir, your formula works. But it has been exclusively designed with column B. This was a trial sample data. Can the formula be made flexible & not be constrained to column B to generate the required answers, please.

 

[AlphaFrog]

flexible & not be constrained to column B

Too vague.

Change the B in the formula to whatever column you like. Otherwise, I don't understand what you want.

Will these cells change too? If yes, explain.
"V94, W94, X94, Y94, AA94, AB94, AC94, AG94, AH94, AI94, AJ94

 

[hsandeep]

Too vague.

Change the B in the formula to whatever column you like. Otherwise, I don't understand what you want. Currently, instead of column B94, the 'actual cell address' is AER107. This cell address AER107 is most likely to ‘change’ it s address location as and when any columns are inserted / deleted before the column AER. That’s the reason of requesting you for a formula which has no constraint with respect to column B (what I mean here the numbers kept in the formulas ..............-21,1,2,3,4,,5,6,7,,,,8,9,10,11 has to be changed ) & takes care of the required result. Cell address AER107 is not permanent, can a formula take care of such situation where address of cell address B97 or cell address AER107 does not become constraint i.e. wherever the 'flag' cell address is kept either at B94 or AER107 or at any other address, the new formula can take care of that?

Will these cells change too? If yes, explain.
"V94, W94, X94, Y94, AA94, AB94, AC94, AG94, AH94, AI94, AJ94 No, these 11 cell address of V94:AJ94 will not change ever. They will remain at this position for ever.