Excel VBA. Highlight cells based on criterion

[EuginG]

Hi! I’m trying to find a simple solution for my Excel VBA script. Sorry for my bad English in advance..

There is an excerpt from my code.

Sub TEST()
Range("E2").Value = Application.WorksheetFunction.CountIfs(Range("G4:G1000"), 100, Range("J4:J1000"), ">0")
End Sub

It puts (in E2 cell) an exact number of cells which have values based on two COUNTIFS criterion. As you see it counts all cells in G column with 100, but only that which also have >0 value in J column.

What next code string should I add to my script to HIGHLIGHT (for example, in RED) those cells in J column, that have <=0 values (of course considering G column with 100 criteria at the same time)?

I already have found some codes for cell’s highlighting, but they are quite complex to me and I don’t know how to integrate it to my script.

Can someone help me, please?

 

[Aroukouth]

Have you considered highlighting these cells with conditional formatting?

 

[EuginG]

Thanks for fast response!
No, I did not. I am quite new to Excel and do not know much about different approaches. I am googling about cond.formatting right now. But… Maybe if you already have a solution for my situation, I would be grateful to hear it sooner ))

 

[Fishboy]

Hi! I’m trying to find a simple solution for my Excel VBA script. Sorry for my bad English in advance..

There is an excerpt from my code.

Sub TEST()
Range("E2").Value = Application.WorksheetFunction.CountIfs(Range("G4:G1000"), 100, Range("J4:J1000"), ">0")
End Sub

It puts (in E2 cell) an exact number of cells which have values based on two COUNTIFS criterion. As you see it counts all cells in G column with 100, but only that which also have >0 value in J column.

What next code string should I add to my script to HIGHLIGHT (for example, in RED) those cells in J column, that have <=0 values (of course considering G column with 100 criteria at the same time)?

I already have found some codes for cell’s highlighting, but they are quite complex to me and I don’t know how to integrate it to my script.

Can someone help me, please?

Hi EuginG, welcome to the boards.

For a VBA solution (as per your original query), the following macro should do what you need. I have incorporated your original code into the macro, although I have tweaked it slightly to fit in with the newer code:

Sub HighlightCells()
' Defines variables
Dim Cell As Range, gRange As Range, jRange As Range


' Sets gRange as G4:G1000
Set gRange = Range("G4:G1000")
' Sets jRange as J4:J1000
Set jRange = Range("J4:J1000")


' Modified version of your original COUNTIFS macro
Range("E2").Value = Application.WorksheetFunction.CountIfs(gRange, 100, jRange, ">0")


' For each cell in gRange
For Each Cell In gRange
    ' If the cell value is 100 and the value 3 columns over is greater than zero then...
    If Cell.Value = 100 And Cell.Offset(0, 3).Value > 0 Then
        ' Fill the cell yellow
        Cell.Interior.ColorIndex = 6
    End If
' Check next cell in gRange
Next Cell


End Sub

 

[Aroukouth]

refer to AdKock's posts in http://www.mrexcel.com/forum/excel-questions/938173-highlight-cell-when-date-over-expire.html

In your case the conditional format formula should be
=AND($G4 = 100, $J4 < 0)

 

[EuginG]

Thanks a lot guys!!! Both of you for quick responses and GREAT solutions (especially from Fishboy). I have understood many useful things.
I've tested the code in my environment, did some corrections considering my values and my whole idea.
And it WORKS nice!!!
For those, who going to do similar operations in Excel I will leave one of my working Modules example. I hope it will help someone too.
Good luck!

Sub HighlightCells()

'Starting the macros from Cleaing all previous highlights
Cells.Interior.ColorIndex = 0
Application.ScreenUpdating = True

'Then defines variables
Dim Cell As Range, gRange As Range, jRange As Range, mRange As Range

Set gRange = Range("G4:G1000")
Set jRange = Range("J4:J1000")
Set mRange = Range("M4:M1000")

Range("E2").Value = Application.WorksheetFunction.CountIfs(gRange, 2, jRange, ">0", mRange, "??")
'"??" at mRange means any two letters (in my case it were OK vs BAD)
'Set a cell to display Name of the value we are searching for (just for info). This cell comes right above E2 cell
Cells(1, 5) = "2,00"

'Set RED borders of the cells where we are planing to get results (just for convenience and nice look)
ActiveSheet.Range("E1").BorderAround _
ColorIndex:=3, Weight:=xlThick
ActiveSheet.Range("E2").BorderAround _
ColorIndex:=3, Weight:=xlThick

'For each cell in gRange set necessary parameters of criterion and color
For Each Cell In gRange
'If the cell value is 2 and the value 3 columns over is lesser or equal zero then...
If Cell.Value = 2 And Cell.Offset(0, 3).Value <= 0 Then
' Fill the entire row of the cell red
Cell.EntireRow.Interior.ColorIndex = 3
End If
'Check next cell in gRange
Next Cell

'Do similar operations for next values

Range("F2").Value = Application.WorksheetFunction.CountIfs(gRange, 2.1, jRange, ">0", mRange, "??")
Cells(1, 6) = "2,10"
ActiveSheet.Range("F1").BorderAround _
ColorIndex:=3, Weight:=xlThick
ActiveSheet.Range("F2").BorderAround _
ColorIndex:=3, Weight:=xlThick

For Each Cell In gRange
If Cell.Value = 2.1 And Cell.Offset(0, 3).Value <= 0 Then
Cell.EntireRow.Interior.ColorIndex = 4
End If
Next Cell

'etc, etc, etc.. for example, for G2,H2,I2,J2,K2

'finally we can get a TOTAL of ALL previous values

Range("L2").Formula = "=Sum(E2,F2,G2,H2,I2,J2,K2)"
Cells(1, 12) = "TOTAL"
ActiveSheet.Range("L1").BorderAround _
ColorIndex:=3, Weight:=xlThick
ActiveSheet.Range("L2").BorderAround _
ColorIndex:=3, Weight:=xlThick

End Sub

 

[Fishboy]

Happy to help