Excel VBA. Highlight cells based on criterion

EuginG

New Member
Joined
May 3, 2016
Messages
14
Office Version
  1. 2016
Platform
  1. Windows
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?:)
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

EuginG

New Member
Joined
May 3, 2016
Messages
14
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Feb 13, 2015
Messages
4,261
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:

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
 

EuginG

New Member
Joined
May 3, 2016
Messages
14
Office Version
  1. 2016
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,168
Messages
5,594,632
Members
413,919
Latest member
ZaxAlchemist

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top