Conditional Formatting, Multiple Values

L

Lara

First, please pardon my absolute inability to properly explain what I am
about to ask. I know next to nothing (or less) about programming.

I am trying to find out if I can make one cell do conditional formatting,
based on five different sets of criteria. Currently, it is set at the
following (please forgive "layman's terms"):

If F21 is between IU26 and IV26, then change cell and font to green.

This works fine.

What I need it to be able to do is perform the conditional formatting based
on one of the following five sets of criteria:


If F19 is between J29 and K29 and if F21 > K31...
or
If F19 is between K29 and L29 and if F21 > L31…
or
If F19 is between L29 and M29 and if F21 > N31…
or
If F19 is between M29 and N29 and if F21 > O31…
or
If F19>N29 and if F21 > P31…

… then change cell and font to green.


Is this even possible?? And if so, how do I go about making it so?
 
C

crazybass2

1.) Is the first condition seperate from the groups below?

2.) Which cell (F19 or F21 or both) do you want to change the cell/font
color to green?

Mike
 
C

crazybass2

Lara,

Sub Worksheet_Change(ByVal Target As Range)
Dim F19 As Range, F21 As Range
If Not Intersect(Target, Union(Range("F19"), Range("F21"))) Is Nothing Then
MeetsCondition = False
Set F19 = Range("F19")
Set F21 = Range("F21")
If Range("IU26") <= F21 And F21 <= Range("IV26") Then MeetsCondition = True
If Range("J29") <= F19 And F19 < Range("K29") And F21 >= Range("K31") Then
MeetsCondition = True
If Range("K29") <= F19 And F19 < Range("L29") And F21 >= Range("L31") Then
MeetsCondition = True
If Range("L29") <= F19 And F19 < Range("M29") And F21 >= Range("N31") Then
MeetsCondition = True
If Range("M29") <= F19 And F19 < Range("N29") And F21 >= Range("O31") Then
MeetsCondition = True
If Range("N29") <= F19 And F21 >= Range("P31") Then MeetsCondition = True
If MeetsCondition Then
F19.Interior.ColorIndex = 4
F19.Font.ColorIndex = 4
Else
F19.Interior.ColorIndex = Default
F19.Font.ColorIndex = Default
End If
End If
End Sub


This code includes your initial Conditional Format. Take note of the
inequalities (<, <=, >=, >) and adjust to suit your needs. The code will
also change the cell back to the default (Black Font, No Fill) if none of the
conditions are met. This code needs to go into the VBA for the sheet you
want the conditional formats to apply (Right Click the sheet and click "View
Code", then paste).
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top