Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Conditional Formating Using Logical Expression
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Conditional Formating Using Logical Expression
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 -- Hope this helps. If it does, please click the Yes button. Thanks in advance for your feedback. Gary Brown "599R" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Conditional Formating Using Logical Expression
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. "Gary Brown" wrote: 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 -- Hope this helps. If it does, please click the Yes button. Thanks in advance for your feedback. Gary Brown "599R" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Conditional Formating Using Logical Expression
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Conditional Formatting Using Logical Expression
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
logical expression help please | New Users to Excel | |||
Can I use more complex logical expression for sumif as creteria? | Excel Worksheet Functions | |||
Logical Expression For MULTIPLE Cells??? | Excel Programming | |||
How do you determine if a field is blank in a logical expression. | Excel Discussion (Misc queries) | |||
Issue with representing a blank in a logical expression. | Excel Programming |