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
0
Utf
3/16/2010 7:27:01 PM
excel.programming 6508 articles. 2 followers. Follow

4 Replies
1139 Views

Similar Articles

[PageSpeed] 38

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
0
Utf
3/16/2010 8:10:02 PM
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
0
Utf
3/16/2010 8:24:01 PM
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
0
Wouter
3/16/2010 8:54:44 PM
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
0
Utf
3/16/2010 8:59:02 PM
Reply:

Similar Artilces:

VBA to check for latest version of front end?
I have a split database where the users have a local copy of the frontend. I would like to use VBA to check whether the users have the mostrecent version. I have set up the following tables:tVersion-Back-End (link to table in the BackEnd version)tVersion-FrontEnd (local table in the FrontEnd version)Each has a field Version where I am planning to put in the versionnumbers.Now, I am just starting to use VBA in my Access projects. Can someonepoint me to some coding to accomplish my task above? Can someone alsopoint me to a good resource (book, website, course) that would take mefrom a beginn...

VBA training suggestions?
I'm looking for tips on software out there on learning VBA for Excel. I know how to create macros and have a good idea of what a script might look like for the macro I created. I just want to learn how to write them on my own for my personal use. Any suggestions? Thank you kindly for your input! I do not know of any software for learning VBA, but you can't go wrong looking at all the Excel VBA web sites as well as purchasing a copy of John Walkenbach's "Excel 2003 Power Programming with VBA" http://j-walk.com/ss/. Recording macros and then editing them is a pow...

Unable to open Outlook Express after upgrade to Win. XP
I upgraded from windows98 to windows XP Pro. Now when I try to use OE I get two error messages. "Outlook Express could not be started. The application was unable to open the OE message store. Your compter may be out of memory or your disk is full.....0x800c012e,3" "OE could not be started because MSOE.DLL could not be initialized. OE maynot be installd correctly" I found a similiar problem fix for window2000 which said to delete OE, rename the old folder, then edit the registry and reinstall. I did the above but was not allowed to rename the old folder and th...

Using mouse wheel in VB editor
Just tried using the mouse wheel to scroll within visual basic editor in excel but it wont let me. How can I turn this feature on. I am using Excel 2003. Thanks in advance. This is a known problem with the latest MS mouse drivers. You can either install version 4 of the drivers (current version is 5), or use FreeWheel, and freeware program at http://www.geocities.com/SiliconValley/2060/freewheel.html . -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "fullers" <fullers@discussions.microsoft.com> wrote in message ne...

Send to certain person using specific email account
I send an email to a certain receipient but don't want to use the default email account. Is it possible that when I send to them (plain text) , that it will choose the other account I want to use to send it? Please advise how I set this up. Thanks. I do not believe that this can be achieved automatically through standard use of Outlook as you need to manually specify the name of the account to send from if different than the default. However, it would be possible using the Outlook object model. An Addin would need to be developed that monitored each mail item prior to sending. If the d...

VBA to creating autotext entries or quickparts in different catego
I have a VBA application that basically allows people to easily create autotext entiries, move them between machines and use them making comments on assignments. Currently it operates in EXACTLY the same way in Word 2003 and 2007 (using userforms) and I want to keep that as long as possible. You can see the application at http://emarking-assistant.baker-evans.com and either the screen image or the video demos will give you an idea of what I am doing Currently I store all the comments in a long list of autotext entries that is displayed in a field with the value of the entr...

Use exchange from a trusted domain
Hi folks, I have domain a and domain b. Domain a is all set upp with exchange etc. Domain b was bought, and they don't have exchange. I have trust between the domains and file sharing and authentication are ok. What we want to do is, make users on domain b and let them use the exchange (on a seperate storage) on domain a. The users on domain b must logon to domain b, but need to have mailbox on domain a. Domain a are running on win2003 and exchange 2003. Domain are running on win2003 with forest and domain ready for exchange. Any advices? thanks in advance, - Bjarni Hi, Thi...

Non-VBA way of making custom menus.
Sorry for the new thread but even on Google, the thread isn't showing up. Well, it was SOOO easy, as I knew it would be. The webpage I quoted in my message this morning didn't mention the "New Menu" at all! So, here is the non-VBA way to create a custom menu: - TOOLS > CUSTOMIZE - under the categories available choose NEW MENU and then drag the NEW MENU option under the Commands window up to the menu bar (like D'UH!! <lol> Wish all those hours spent searching yielded webpages that gave this! <g>) - THEN one can go back to the MACROS category a few line...

Using Access 2003 on client to see Sharepoint 2007
Testing a Sharepoint environment and I can easily manipulate and run the database from Access 2007, but when I try to use Access 2003 it wants me to save the database to a location. The Access 2003 has the compatiblity pack, but it seems to not work when I download it. So the question is using Sharepoint 2007 and posting the Access 2007 format to it, can a user go to the sharepoint site and use a 2003 Access database that has the compatibility pack and open without having to download it? -- Message posted via http://www.accessmonster.com Send check for $500.00 to: David A Jenn...

Fractions Number format
Is it possible to do a custom number format for cells such that fractions are displayed. I'm guessing not because there'd be a limit on what fractions you could display, but I would probably only be interested in showing a half as the little 1/2 (alt,0189 I think) rather than .5 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.478 / Virus Database: 275 - Release Date: 06/05/03 Lee There are a limited number of fractions that can be shown under Format>Cells>Fractions. Gord Dibben Excel MVP - XL97 SR2 & ...

Some Excel formatting functions taking a long time to work #2
Excel 2000 SP3 When I use some formatting functions for the first time in a session, such as bold, increasing font size etc, it takes up to 30 seconds to work. Meantime Excel is locked up until it completes that formatting call. I suspect faulty DLL? Has anyone experienced this? How to fix (other than a complete re- install) ? Any advice appreciated. Piri On Nov 5, 8:39=A0am, Piri <wiremu.pare...@hotmail.com> wrote: > Excel 2000 SP3 > When I use some formatting functions for the first time in a > session, such as bold, increasing font size etc, it takes =A0up to 30 > secon...

compact database in VBA access 2007
Hello, I have recently upgrade to office 2007 from office 2003. To compact a database from within the database itself, I used the follwing code. Unfortunately it no longer works in access 2007. Is there some similar code that will work? Public Function FncCompactTheCurrentDB() CommandBars("Menu Bar"). _ Controls("Tools"). _ Controls("Database utilities"). _ Controls("Compact and repair database..."). _ accDoDefaultAction End Function Thank You, SL On Thu, 28 Jan 2010 17:34:01 -0800, SL <SL@discussions.microsoft....

VBA to put a copy of worksheet on the desktop 05-13-10
Hi all, In my workbook XYZ I have a sheet ABC. With a button on sheet DEF I can refresh sheet ABC. When the code finishes it job I want to add the actual date (short European notation dmyy) and time (f.i. 241110 16.31) to the name of the sheet (which becomes ABC 241110 16.31) and after that make a copy of that sheet in a separate workbook and put that workbook as an icon on the desktop of my computer. Is this possible? If so, please help me with the necessary code. Thanks in advance for your assistance. Jack Sons The Netherlands ...

How to save Japanese characters in CSV format of Excel ?
Hi, I would like to know, how to save the Doubly-Byte characters.For example, Japanese in CSV of Excel.Here are the steps, I tried. 1. Open Excel 2. Copy pasted the Japanese charactesrs in a cell.It is displaying the characters correctly. 3. Save as CSV(Comma seperated file) It is saving as ??????. Thanks in advance for the info. ...

How Use ShellExecute with a CMemFile
Hello, I need to store files of various types (such as .doc, .xls, .jpg, .pdf, ..wmv, ...) in a database. Upon retrieval of such a file I have the file as data in memory in a CMemFile. I need to then "display" the file in the aproriate application. If I were to write the CMemFile to disk and create an actual file I could then, of course, use ShellExecute() to launch , say Word to display a .doc file. However, I would much prefer to avoid the overhead of writing the data to an actual file and then dealing with having to detect when the User is done and cleaning up the file I had ...

Can i use conditional formating on a cell when it contains a formula?
I am trying a "conditional formatting" on a cell that contains formula, but it didn't work. "If cell value is equal to 0 then font - white" This doesn't work, stays always. If i use this condition on a cell without formula it works just fine. Thank -- si ----------------------------------------------------------------------- sit's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=262 View this thread: http://www.excelforum.com/showthread.php?threadid=26784 Hi are you sure your formula returns an exact zero?. Could you post the formul...

Can't configure Outlook Express
I had Outlook express set up for the Money newsgroup, but somehow I lost it. Now I even forgot how to set it up again. I always get the error message saying "server cannot be found". The server name I typed in was microsoft.public.money, and I am not sure what should I type in here Thanks for help In microsoft.public.money, wj wrote: >I had Outlook express set up for the Money newsgroup, but >somehow I lost it. Now I even forgot how to set it up >again. I always get the error message saying "server >cannot be found". The server name I typed in was &g...

Using INDIRECT within functions
I learned a bit from JLathman in a previous post but tried to use the same methodology with this formula (also learned from this Group to get the last entered value in a column) without much luck. The original formula was this with 2009 sheet being static: =LOOKUP(2,1/('2009'!H3:H65536<>""),'2009'!H3:H65536) Trying to make it dynamic I tried the following without success: =LOOKUP(INDIRECT("2,1/(" & O1 & "!$H$3:$H$65536<>"")," & O1 & "!$H$3:$H$65536)")) Did I miss something or are...

Date format 04-11-06
Hi, Is there a possibility that the dates used in all the entities are not in the default format mm/dd/yyyy but in dd/mm/yyyy. I already adapted the Organisatonal settings, that only adapts the journal but nog the dates of an appointment. Does anyone have an idea? Thanks, ...

Help with simple(?) VBA function
I'm trying to selectively BOLD cells by the use of a User-Defined function. No joy. The VBA Help topics suggest something like this: Function Bold() Worksheets("Sheet1").Range("A1:A5").Font.Bold = True End Function When I try to use it the referenced cells are not changed and the function returns "0". Can anyone point this VBA neophyte in the right direction? Thanks, -Dick- Hi Dick, A function can only return a value. Macros and Functions (Macros as Opposed to Functions) http://www.cpearson.com/excel/differen.htm If all...

Determine a result of one column based on conditions in two column
Example Col A Col B Count the number of a's in Col B only when an x is in Col A x a x a Result should be 2 y a z p I can't figure it out x t x m Thanks try this =SUMPRODUCT(--(A2:A7="x"),--(B2:B7="a")) -- Hope this help Please click the Yes button below if this post have helped answer your needs Thank You cheers, francis "tel703" wrote: > Example > Col A Col B Count the number of a...

How to use count for calls per hour
I am trying to create a pivot table and chart that will show me the # of calls per hour by day for a range of dates Aug 1 - Sept 19. My data looks like this where each line is the date and time of the call: How do I get a count of the # of the calls per hour by day? 8/1/08 12:48 AM 8/1/08 1:53 AM 8/1/08 2:36 AM 8/1/08 3:24 AM 8/1/08 3:37 AM 8/1/08 4:04 AM 8/1/08 4:44 AM 8/1/08 4:55 AM 8/1/08 4:58 AM 8/1/08 7:02 AM 8/1/08 7:28 AM 8/1/08 7:43 AM 8/1/08 7:47 AM 8/1/08 7:56 AM 8/1/08 7:58 AM 8/1/08 8:13 AM 8/1/08 8:33 AM 8/1/08 9:07 AM 8/1/08 10:28 AM 8/1/08 10:38 AM 8/1/08 10:44 AM 8/1/08 ...

How export all mailbox-enabled users from the GAL using Outlook?
Hello, We have a user which, for administrative reasons, needs to export all mailbox-enabled users in the GAL now and then. They just need all the names. Is there a straightforward way for them to do that? We don't want to give them any special permissions and want to avoid server scripting. Ideally, they should be able to export it to a CSV file or any text file. Thanks, - Alan. Alan wrote: > We have a user which, for administrative reasons, needs to export all > mailbox-enabled users in the GAL now and then. They just need all the > names. Is there a straightforward way fo...

copy values generated by conditional formula in one sheet to the other work sheet as values
Hi Everybody, I have data generated by conditional formulae in work sheet1 in columns A to J. If the condition is satisfied the cell will display a realnumber, if the condition doesn't satisfied the cell will display the text"FALSE". Now I wanted to copy the cells which have the real numbers in sheet1 to sheet2 as values(as we do with paste special and paste the values) Do we have any formula or other method to copy the cells in sheet1 to sheet2. can anybody helpme out in this issue. Thanks and Regards Ramana Select your range to copy edit|goto|special|c...

Formatting in CSV
Hi I create a Comma Separated Value file (csv) from my VB application, then open that file with Excel. Excel recognises the commas, and splits the records into separate fields as expected. Can I set properties for rows or columns or cells ? Like bolding, or font size, etc. Thanks Robert -- RobertLees ------------------------------------------------------------------------ RobertLees's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26580 View this thread: http://www.excelforum.com/showthread.php?threadid=486488 Yes. Even though the .csv file contains no fo...