Examples
Highlight differences
option explicit
sub main() ' {
cells(1,1) = "foo" : cells(1,2) = "bar": cells(1,3) = "baz" : cells(1,4) = 42 : cells(1,5) = 18
cells(2,1) = "foo" : cells(2,2) = "bar": cells(2,3) = "baz" : cells(2,4) = 99 : cells(2,5) = 18
dim fcs as formatConditions
set fcs = range(cells(2,1), cells(2,5)).formatconditions
'
' German Excel: use "=Z(-1)S" (https://stackoverflow.com/a/48539578/180275)
' for formula.
'
dim fc as formatCondition
set fc = fcs.add(xlCellValue, xlNotEqual, "=R[-1]C")
fc.interior.color = rgb(255, 170, 170)
end sub ' }
Github repository about-MS-Office-object-model, path: /Excel/FormatCondition/highlight-differences.vb
Greater than
' ..\..\..\runVBAFilesInOffice.vbs -excel greater_than -c main
public sub main()
dim cond as formatCondition
range("a1:a10").formula = "=rand()"
set cond = range("a1:a10").formatConditions.add(type := xlCellValue, operator := xlGreater, formula1 := "=0.5")
cond.font.color = rgb(255, 0, 0)
cond.interior.color = rgb(200, 200, 200)
activeWorkbook.saved = true
end sub
Github repository about-MS-Office-object-model, path: /Excel/FormatCondition/greater_than.bas
Higlight non-number cells
The following example combines the the worksheet functions
not and isNumber to highlight cells that contain nun numerical values.
The range in which the conditional formatting is applied is visually set apart by a drawn border around it.
option explicit
sub main() ' {
dim rng as range
set rng = range(cells(4,3), cells(7, 7))
rng.borderAround xlContinuous, xlMedium, color := rgb(100, 150, 200)
highlightNonNumbersInRange rng
cells(4,2) = "foo"
cells(4,3) = "bar"
cells(4,4) = "baz"
cells(3,5) = 42
cells(4,5) = 99
cells(5,5) = 13.12
cells(7,4) = "hello"
cells(8,4) = "world"
cells(6,6) = "xyz"
cells(7,6) = 12345678
cells(10,10).select
end sub ' }
sub highlightNonNumbersInRange(rng as range) ' {
dim formula as string
' formula = "=not(isNumber(" & rng.cells(1).address(rowAbsolute := false, columnAbsolute := false) & "))"
formula = "=not(isNumber(r[0]c[0]))"
dim fc as formatCondition
set fc = rng.formatConditions.add(xlExpression, formula1 := formula)
fc.font.color = rgb(230, 20, 40)
end sub ' }
Github repository about-MS-Office-object-model, path: /Excel/FormatCondition/not-number.vb
Using the Immediate Window to quickly highlight cells
It's possible to quickly highlight cells without writing a VBA macro but by rather executing the code in the immediate window.
The following example simply highlights call cells that contain a value:
set cond = selection.formatConditions.add(xlCellValue, xlNotEqual, "=""""") ' = "" cond.interior.color = rgb(255, 130, 160)
Later:
cond.delete
Highlight differences (2)
Again highlighting differences between the the values in the row above and the current row, but this time using the immediate window:
c = application.international(xlListSeparator) set cond = selection.formatConditions.add( type:= xlExpression, formula1 := "=indirect(address(row()" & c & " column())) <> indirect(address(row()-1" & c & "column()))") cond.interior.color = rgb(255, 140, 190)
The conditional formatting can later be deleted:
cond.delete
This example is using the
indirect, address, row and column worksheet function so that values are compared even if cells are moved around. Highlighting differences in two ranges
An example that demonstrats how differences in two ranges are highlighted is demonstrated here.