count if variable range vba

Question

I am trying to implement conditional formatting in VBA using a countif formula. It does work for the moment but I would like to implement a variable range. I have tried the following without success

 With Range("AI7").FormatConditions _
        .Add(xlExpression, Formula1:="=AND(COUNTIF($C$7:$AG$7;""B"")<=3;COUNTIF($C$7:$AG$7;""R"")<=5)")
        .Interior.Color = RGB(185, 207, 203)
  End With

How to make the range within the countif dynamic. I have tried that:

With Range("AI7").FormatConditions _
        .Add(xlExpression, Formula1:="=COUNTIF(R[7]C[3], R[7]C[31])="B"")
        .Interior.Color = RGB(248, 194, 203)
    End With

I would like to implement a variable range - can you be more specific about this? — Scott Holtzman
– Scott Holtzman, Commented Jul 21, 2016 at 19:58
instead of writing C7:AG7 i would like to make AG dynamic, i.e. related to a variable. I tried the notation below but it does not seem to work — richpiana
– richpiana, Commented Jul 21, 2016 at 20:05
Try Formula:="=COUNTIF($c$7:" & cells(r,c).Address & ";""B"")" using r, and c as the row and column numbers of the last cell in your dynamic range. — Tim
– Tim, Commented Jul 21, 2016 at 20:12

Scott Holtzman · Accepted Answer · 2016-07-21 20:14:48Z

1

This is one way to do it. You'll have to grab the column letter(s) dynamically in whichever way you need, but you can put that into the sCol variable, below.

Dim sCol as String

 sCol = "AG" 'you'll have to define the column in whatever way you need

 With Range("AI7").FormatConditions _
        .Add(xlExpression, Formula1:="=AND(COUNTIF($C$7:$" & sCol & "$7;""B"")<=3;COUNTIF($C$7:$" & sCol & "$7;""R"")<=5)")
        .Interior.Color = RGB(185, 207, 203)
  End With

answered Jul 21, 2016 at 20:14

Scott Holtzman

27.3k5 gold badges42 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

count if variable range vba

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related