Conditional Formatting Using VBA

[Denny57]

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.

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

 

[DanteAmor]

I want to assume that you need that:

You will need to run the ConditionalFormatting_Click macro every time you want to update everything.

Sub ConditionalFormatting_Click()
  Cells.FormatConditions.Delete
  Call WeightConditionalFormatting
  Call BloodOxygenConditionalFormatting
  Call PulseRateConditionalFormatting
End Sub


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

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

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

 

[Denny57]

Dante

Thank you for pointing out the typos in Module 2, however, the values in Column C are all in the range 97 to 99 but are formatted as Format Condition 1. This would be the expected format for the values in module 1.

Additionally, Module 3 is only returning formatting in 2 cells whose values are 63 & 64 (Black Font on Red) which is not in the code. no other cells are formatted.

These anomalies are what is confusing me

 

[Denny57]

Yes I am happy to run the macro each day as I enter the daily records. I assume that thi could be automated but given that I have 17 modules, I think that the current option might be less stressful to code

 

[Denny57]

Incidentally, please can you explain the relevance of (3) after .End. I assumed that it rferred to the number of conditions, but maybe not as one Module only contans 2 conditions

 

[Denny57]

Apologies, end of a long day, I had not realised that each macro has been added to in to a single module. This appears to be working fine now.

I will tidy up the coding after a good night rest and when I am refreshed

Thank you again, however I would like to understand the signifiance of .End(3) as some additional macros will have more than 3 conditions

 

[DanteAmor]

however I would like to understand the signifiance of .End(3)

The number 3 is the numeric value for the xlUp parameter:

You can use xlUp or 3:
xlDown = 4
xlToLeft = 1
xlToRight = 2
xlUp = 3

as some additional macros will have more than 3 conditions

The number (3) is to find (among other things) the "last row with data" of a certain column. It has nothing to do with the number of conditional formatting rules.