Conditional Formatting Using VBA

D

Denny57

Board Regular
Joined
Nov 23, 2015
Messages
246
Office Version
  1. 365
Platform
  1. Windows
I found some VBA code that will apply conditional formatting which uses VB colours and for which I have been reqired so far to apply a fixed range. However, I have encountered some limitations which I am not sure how to overcome.
1) The first condition applies a fill colour when a cell value is null
2) I would like to change the font colour to white when the interior colour is dark.

Ideally I would like to apply conditional formatting only to cells which contain a value and remove the fixed range with a dynamic range. I beleive both these requirements can be achieved by removing the fixed range.


VBA Code: 
Sub WeightConditionalFormatting()
Dim wsDailyRecords  As Worksheet
Dim MyRange As Range
Set MyRange = Range("B4:B400")
'First Condition
MyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula2:="71"
MyRange.FormatConditions(1).Interior.Color = vbBlue
'Second Condition
MyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, Formula1:="72", Formula2:="74"
MyRange.FormatConditions(3).Interior.Color = vbYellow
'Third Condition
MyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="74"
MyRange.FormatConditions(4).Interior.Color = vbRed
End Sub

Thanking in advance
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Sort by date Sort by votes
Try:

VBA Code: 
Sub WeightConditionalFormatting()
  Dim wsDailyRecords  As Worksheet
  Dim MyRange As Range
  
  Set MyRange = Range("B4", Range("B" & Rows.Count).End(3))
  Cells.FormatConditions.Delete
  
  'First Condition
  MyRange.FormatConditions.Add Type:=xlExpression, Formula1:="=($B4<72)*($B4<>"""")"
  MyRange.FormatConditions(1).Interior.Color = vbBlue
  MyRange.FormatConditions(1).Font.Color = vbWhite
  'Second Condition
  MyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, Formula1:="72", Formula2:="74"
  MyRange.FormatConditions(2).Interior.Color = vbYellow
  'Third Condition
  MyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="74"
  MyRange.FormatConditions(3).Interior.Color = vbRed
  MyRange.FormatConditions(3).Font.Color = vbWhite
End Sub
 
Upvote 0
Thank you very much.

As ever I will keep this resolution for future reference
 
Upvote 0
Dante

Unfortunately I do still have a problem. I had created multiple modules, one for each column as the conditional formatting was to be different for each column.

These modules were called from a separate module which was assigned to a command button

I have removed most of the modules pending updating however, the formatting is being applied across every column in the workheet, even when just the 2 modules below are in the macro

I have a couple of questions as there will be a total of 16 Modules, each for a different column.
1) Is it possible to include each sub routine in a single module as I would do for a user Form command button
2) What is causing the formatting to be applied to all the columns ( format Interior Red, Font White is being applied to these row)

Module 1
VBA Code: 
Sub WeightConditionalFormatting()
  Dim wsDailyRecords  As Worksheet
  Dim MyRange As Range
  
  Set MyRange = Range("B4", Range("B" & Rows.Count).End(3))
  Cells.FormatConditions.Delete
  
  'First Condition
  MyRange.FormatConditions.Add Type:=xlExpression, Formula1:="=($B4<72)*($B4<>"""")"
  MyRange.FormatConditions(1).Interior.Color = vbGreen
  MyRange.FormatConditions(1).Font.Color = vbBlack
  'Second Condition
  MyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, Formula1:="72", Formula2:="74"
  MyRange.FormatConditions(2).Interior.Color = vbYellow
  MyRange.FormatConditions(2).Font.Color = vbBlack
  'Third Condition
  MyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="74"
  MyRange.FormatConditions(3).Interior.Color = vbRed
  MyRange.FormatConditions(3).Font.Color = vbWhite
End Sub

Module 2
VBA Code: 
Sub BloodOxygenConditionalFormatting()
Dim wsDailyRecords  As Worksheet
Dim MyRange As Range
Set MyRange = Range("C4", Range("C" & Rows.Count).End(2))
'Cells.FormatConditions.Delete

'First Condition
MyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, Formula1:="95", Formula2:="99"
MyRange.FormatConditions(1).Interior.Color = vbGreen
MyRange.FormatConditions(1).Font.Color = vbBlack
'Second Condition
MyRange.FormatConditions.Add Type:=xlExpression, Formula1:="=($B4<95)*($B4<>"""")"
MyRange.FormatConditions(2).Interior.Color = vbRed
MyRange.FormatConditions(2).Font.Color = vbWhite
End Sub

Many thanks
 
Upvote 0
Incidentally, I tried Module 1 with vbGreen and then RBG 0,176,80 but vbGreen remained as the interior colour
 
Upvote 0
Dante, Please ignore th elast 2 messages as I found a typo in my code, however, I may have another issue which I am investigating
 
Upvote 0
Hi Dante.. Ok so the code works ok for a single module, however I have 17 columns each of which has a different conditional formatting requirement.

Modules 1 and 2 (below) work fine apparently, but module 3 applied incorrect formatting. Please can I ask a couple of questions to help clarify the code

In Module 1, please can you explain the purpose of .End(3)). The module has 3 different conditions but seeing how the code is processing data, I do not think that this relates to the number of conditions.

Module 1 also contains Cells.FormatConditions.Delete. Please advise of the purpose of this line of code as it impacts the function of other modules.

Module 1
VBA Code: 
Sub WeightConditionalFormatting()
  Dim wsDailyRecords  As Worksheet
  Dim MyRange As Range
    Set MyRange = Range("B4", Range("B" & Rows.Count).End(3))
  Cells.FormatConditions.Delete
    'First Condition
  MyRange.FormatConditions.Add Type:=xlExpression, Formula1:="=($B4<72)*($B4<>"""")"
  MyRange.FormatConditions(1).Interior.Color = RGB(192, 0, 0)
  MyRange.FormatConditions(1).Font.Color = RGB(255, 255, 255)
  'Second Condition
  MyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, Formula1:="72", Formula2:="74"
  MyRange.FormatConditions(2).Interior.Color = vbYellow
  MyRange.FormatConditions(2).Font.Color = vbBlack
  'Third Condition
  MyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="74"
  MyRange.FormatConditions(3).Interior.Color = vbRed
  MyRange.FormatConditions(3).Font.Color = vbWhite
End Sub

Module 2
VBA Code: 
Sub BloodOxygenConditionalFormatting()
Dim wsDailyRecords  As Worksheet
Dim MyRange As Range
Set MyRange = Range("C4", Range("C" & Rows.Count).End(2))
'Cells.FormatConditions.Delete
'First Condition
MyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, Formula1:="95", Formula2:="99"
MyRange.FormatConditions(1).Interior.Color = vbGreen
MyRange.FormatConditions(1).Font.Color = vbBlack
'Second Condition
MyRange.FormatConditions.Add Type:=xlExpression, Formula1:="=($C4<95)*($C4<>"""")"
MyRange.FormatConditions(2).Interior.Color = vbRed
MyRange.FormatConditions(2).Font.Color = vbWhite
End Sub

Module 3
This applies condition 3 to the values specified in condition 1 and no other cells are formatted.

VBA Code: 
Sub PulseRateConditionalFormatting()
Dim wsDailyRecords  As Worksheet
Dim MyRange As Range
Set MyRange = Range("D4", Range("D" & Rows.Count).End(3))
'First Condition
MyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, Formula1:="66", Formula2:="73"
MyRange.FormatConditions(1).Interior.Color = vbGreen
MyRange.FormatConditions(1).Font.Color = vbBlack
'Second Condition
MyRange.FormatConditions.Add Type:=xlExpression, Formula1:="=($D4<66)*($D4<>"""")"
MyRange.FormatConditions(2).Interior.Color = vbYellow
MyRange.FormatConditions(2).Font.Color = vbBlack
'Third Condition
MyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="73"
MyRange.FormatConditions(3).Interior.Color = vbRed
MyRange.FormatConditions(3).Font.Color = vbWhite
End Sub

I have tried various combinations and formats but nothing seems to apply the correct formatting after Modules 1 & 2
 
Upvote 0
Further Observations

I am gradually including additional Modules in the Macro (Module) which is assigned to the command button to apply the conditional formatting. I have also ascertained that each rule for each column needs to be coded in a specific order. Thus I have changed the order of the code with the following results.

Module 1 = appears tofunction correctly
After that Module 2 (Column C) applies the formatting rules for Module 1 and if I change a cell value to that of the first condition in module 1, then I get the formatting expected for the first condition in module 1 and this is applied to all rows in that column ( to the right of that cell.

It now appears that Module 17 (used to run the macro (Module) is perhaps not functioning as expected.
MODULE 17
VBA Code: 
Sub ConditionalFormatting_Click()
Call WeightConditionalFormatting
Call BloodOxygenConditionalFormatting
Call PulseRateConditionalFormatting
End Sub

Module 1
VBA Code: 
Sub WeightConditionalFormatting()
  Dim wsDailyRecords  As Worksheet
  Dim MyRange As Range
 
  Set MyRange = Range("B4", Range("B" & Rows.Count).End(3))
  'Cells.FormatConditions.Delete
  'First Condition
  MyRange.FormatConditions.Add Type:=xlExpression, Formula1:="=($B4<72)*($B4<>"""")"
  MyRange.FormatConditions(1).Interior.Color = RGB(0, 176, 80)
  MyRange.FormatConditions(1).Font.Color = RGB(0, 0, 0)
  'Second Condition
  MyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, Formula1:="72", Formula2:="74"
  MyRange.FormatConditions(2).Interior.Color = vbYellow
  MyRange.FormatConditions(2).Font.Color = vbBlack
  'Third Condition
  MyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="74"
  MyRange.FormatConditions(3).Interior.Color = vbRed
  MyRange.FormatConditions(3).Font.Color = vbWhite
End Sub

Module 2
VBA Code: 
Sub BloodOxygenConditionalFormatting()
Dim wsDailyRecords  As Worksheet
Dim MyRange As Range
Set MyRange = Range("C4", Range("C" & Rows.Count).End(2))
'Cells.FormatConditions.Delete
'First Condition
MyRange.FormatConditions.Add Type:=xlExpression, Formula1:="=($C4<95)*($C4<>"""")"
MyRange.FormatConditions(1).Interior.Color = vbRed
MyRange.FormatConditions(2).Font.Color = vbWhit
'Second Condition
MyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, Formula1:="95", Formula2:="99"
MyRange.FormatConditions(3).Interior.Color = vbGreen
MyRange.FormatConditions(4).Font.Color = vbBlack
End Sub

Module 3
VBA Code: 
Sub PulseRateConditionalFormatting()
Dim wsDailyRecords  As Worksheet
Dim MyRange As Range
'Cells.FormatConditions.Delete
Set MyRange = Range("D4", Range("D" & Rows.Count).End(3))
'First Condition
MyRange.FormatConditions.Add Type:=xlExpression, Formula1:="=($D4<66)*($D4<>"""")"
MyRange.FormatConditions(1).Interior.Color = vbBlue
MyRange.FormatConditions(1).Font.Color = vbBlack
'Second Condition
MyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, Formula1:="66", Formula2:="73"
MyRange.FormatConditions(2).Interior.Color = RGB(0, 176, 80)
MyRange.FormatConditions(2).Font.Color = RGB(0, 0, 0)
'Third Condition
MyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="73"
MyRange.FormatConditions(3).Interior.Color = vbRed
MyRange.FormatConditions(3).Font.Color = vbWhite
End Sub
Any suggestions or solutions would be most welcome
 
Upvote 0
VBA Code: 
Sub BloodOxygenConditionalFormatting()
Dim wsDailyRecords  As Worksheet
Dim MyRange As Range
Set MyRange = Range("C4", Range("C" & Rows.Count).End(2))
'Cells.FormatConditions.Delete
'First Condition
MyRange.FormatConditions.Add Type:=xlExpression, Formula1:="=($C4<95)*($C4<>"""")"
MyRange.FormatConditions(1).Interior.Color = vbRed
MyRange.FormatConditions(2).Font.Color = vbWhit
'Second Condition
MyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, Formula1:="95", Formula2:="99"
MyRange.FormatConditions(3).Interior.Color = vbGreen
MyRange.FormatConditions(4).Font.Color = vbBlack
End Sub

Module 3
VBA Code: 
Sub PulseRateConditionalFormatting()
Dim wsDailyRecords  As Worksheet
Dim MyRange As Range
'Cells.FormatConditions.Delete
Set MyRange = Range("D4", Range("D" & Rows.Count).End(3))
'First Condition
MyRange.FormatConditions.Add Type:=xlExpression, Formula1:="=($D4<66)*($D4<>"""")"
MyRange.FormatConditions(1).Interior.Color = vbBlue
MyRange.FormatConditions(1).Font.Color = vbBlack
'Second Condition
MyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, Formula1:="66", Formula2:="73"
MyRange.FormatConditions(2).Interior.Color = RGB(0, 176, 80)
MyRange.FormatConditions(2).Font.Color = RGB(0, 0, 0)
'Third Condition
MyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="73"
MyRange.FormatConditions(3).Interior.Color = vbRed
MyRange.FormatConditions(3).Font.Color = vbWhite
End Sub
 
Upvote 0
Module 2, you have typo errors:

Rich (BB code): 
Sub BloodOxygenConditionalFormatting()
Dim wsDailyRecords  As Worksheet
Dim MyRange As Range
Set MyRange = Range("C4", Range("C" & Rows.Count).End(3))
'Cells.FormatConditions.Delete
'First Condition
MyRange.FormatConditions.Add Type:=xlExpression, Formula1:="=($C4<95)*($C4<>"""")"
MyRange.FormatConditions(1).Interior.Color = vbRed
MyRange.FormatConditions(1).Font.Color = vbWhite
'Second Condition
MyRange.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, Formula1:="95", Formula2:="99"
MyRange.FormatConditions(2).Interior.Color = vbGreen
MyRange.FormatConditions(2).Font.Color = vbBlack
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Conditional Formatting Macro
Replies
2
Views
442
rlv01
R
J
  • Question Question
Conditional formatting Coloring due dates
Replies
4
Views
206
JAZ91
J
J
  • Question Question
Multiple conditional formatting
Replies
6
Views
305
StephenCrump
S
H
  • Solved
Conditionally Format Multiple Columns
Replies
5
Views
845
rollis13
rollis13
M
  • Solved
How to keep conditional formatting formulas from automatically changing
Replies
5
Views
202
Kaper
Kaper

Forum statistics

Threads
1,224,802
Messages
6,181,054
Members
453,014
Latest member
Chris258

Share this page

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
Back
Top