VBA Conditional Formating Using Logical Expression

  • Thread starter Thread starter 599R
  • Start date Start date
5

599R

I am new to VBA so please provide as much explanation in your responses as
possible as I would love to learn more.

I have a set of data (call it range 1) that resides in specific cells (all
in one column) and does not change. I have another set of data (call it
range 2) that is spread across 5 columns and occupies the same rows as range
1. Range 2 values in each of the 5 columns relate to the Range 1 data in the
same row. I need to set up more than three conditions using >, <, = or n/a
to determine the color of each of the cells in range 2.

As data is entered into the cells of range 2 the interior color of each cell
needs to change based on how it compares to the value found in range 1. I
have used select case to setup conditional formatting in the past, but am
struggling with the setup for this situation. How do I make this work?

I would prefer not to use the conditional formatting tab to carry this task
out as there may be more varioations than that feature will allow.

Please hlep - thank you
 
Using Conditional Formating, you can only have 3 different formats.
Each area you select, however, can have different formatting.
For example, if you want different conditional formatting for each of the 5
columns (with only 3 different formats per column)...

- Assume Range 1 is in column A
- Assume the columns in Range 2 are F, G, H, I and J.
- Assume data starts in row 2
- Let's start with column F...
- Highlight Column F
- select FORMAT > CONDITIONAL FORMATTING...
- change 'Cell Value Is' to 'Formula is'
- put desired formula in the text box
- something like...
=$A1=1
- NOTE that the absolute ($) is only on the 'A' AND there is an '=' sign
after the $A1
 
Gary - Thank you for the help

I was able to acheive the desired results earlier with conditional
formating, but was hoping to make the color coding work through code. I am
preparing a reporting tool for many users and think it will be easier to
build onto or modify the existing setup along the way if I use code.
Additionaly, using code has helped to limit unwanted formatting changes and
errors in the past.

If anybody has a recommendation that uses code, I would appreciate the help.
 
Hi,

In Excel2007 I have created the code below.
I am very sure this will work in Excel 2003.

Open the VBE and copy this code the the Sheet you want to control.

' begin of code
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
' Savety
On Local Error GoTo Change_err

' Stop if more than 1 cell is changed

If Target.Cells.Count > 1 Then Exit Sub

' Stop if changed cell is outside range2
Dim rngInter As Range
Set rngInter = Intersect(Target, Range("range2"))
If rngInter Is Nothing Then Exit Sub

' Check if changed cell is empty
If IsEmpty(Target) Then
FormatEmpty Target
Exit Sub
End If

' stop if changed cell is not numeric
If Not (IsNumeric(Target.Value)) Then
FormatNA Target
Exit Sub
End If

' Compare value of changed cell with range 1
' ------------------------------------------
' Find out row number is changed cell in range 2
Dim lngRow As Long
lngRow = Target.Row
lngRow = lngRow - Range("range2").Cells(1, 1).Row + 1

' Stop if cell in range1 is not numeric
If Not IsNumeric(Range("range1").Cells(lngRow, 1)) Then
FormatNA Target
Exit Sub
End If

' Find row corresponding value in range 1
Dim dblCompare As Double
dblCompare = Range("range1").Cells(lngRow, 1).Value

' Compare with value of changed cell
Select Case Sgn(dblCompare - Target.Value)
Case 1
' range 2 < range 1
FormatLess Target
Case 0
' range 2 = range 1
FormatEqual Target
Case -1
' range 2 > range 1
FormatMore Target
End Select

' normal end of sub
Exit Sub

' error handler (safety)
Change_err:
FormatError Target

End Sub


Sub FormatLess(Target As Range)
With Target
.Interior.Color = vbBlue
With .Font
.Bold = False
.Strikethrough = False
.Color = vbYellow
End With
End With
End Sub

Sub FormatMore(Target As Range)
With Target
.Interior.Color = vbGreen
With .Font
.Bold = True
.Strikethrough = False
.Color = vbBlack
End With
End With
End Sub

Sub FormatEqual(Target As Range)
With Target
.Interior.Color = vbYellow
With .Font
.Bold = False
.Strikethrough = False
.Color = vbBlack
End With
End With
End Sub

Sub FormatNA(Target As Range)
With Target
.Interior.Color = vbBlack
With .Font
.Bold = False
.Strikethrough = True
.Color = vbWhite
End With
End With
End Sub

Sub FormatEmpty(Target As Range)
With Target
.Interior.Color = vbWhite
With .Font
.Bold = False
.Strikethrough = False
.Color = vbBlack
End With
End With
End Sub

Sub FormatError(Target As Range)
With Target
.Interior.Color = vbRed
With .Font
.Bold = True
.Strikethrough = True
.Color = vbYellow
End With
End With

End Sub

'-- end of code

HTH,

Wouter
 
To aid in the understanding of my original questsion I thought I would post
my initial attempt at the code. There are more layers that I hope to add,
but I am having issues with the initial setup. There may be a better way to
do this than using the "case" function. I am open to any suggestions.

Here is a copy of my first try - hope it helps to get across what I am
trying to acheive:

''Highlight late Completes

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Deadline As Range
Dim Actual As Range
If Target.Cells.Count > 1 Then Exit Sub
Actual = Target
Set Deadline = Range("C20") 'change to suit
Set Actual = Range("D20:H20") 'change to suit

If Not Intersect(Target, Deadline) Is Nothing Then
Select Case Actual
Case Is > Deadline
Target.Interior.ColorIndex = 6 'Yellow
End Select
End If
End Sub

Thanks in advance for the help
 
Back
Top