I have too many conditions for Excel
July 22, 2011 6:49 AM Subscribe
Using Excel 2000, I need to do conditional formatting with 5 options. This isn't possible with actual conditional formatting, but it is possible using Visual Basic. Unfortunately, I know nothing about Visual Basic. Specifics inside.
I have two columns:
date1, amt1
date2, amt2
date3, amt3
Date is a rolling field based on the current date, so it changes weekly, and therefore all other fields change weekly as well.
amt is a vlookup from date based on this range of 3 columns elsewhere:
dateX, amtX, statusX
dateY, amtY, statusY
Not all date1/2/3 will have a dateX associated, if so amt1 is 0 (encoded into the vlookup statement, essentially, using isna)
If amt1 is 0, I don't want any particular formatting.
Otherwise, I want the amt1 field to be coloured based on the information in the status column. There are 4 possible statuses (red, yellow, green, blue). All fields which are non-zero will be coloured one of these four colours. No fields which are zero will be.
Googling around, I see things like use VB to get beyond limit of 4 statuses and select case in VB and use vlookup in VB but I am not entirely clear how to put these all together.
The colours have to update automatically when I change the status, I cannot need to run a macro.
I have done some programming before. I have never used visual basic.
I have two columns:
date1, amt1
date2, amt2
date3, amt3
Date is a rolling field based on the current date, so it changes weekly, and therefore all other fields change weekly as well.
amt is a vlookup from date based on this range of 3 columns elsewhere:
dateX, amtX, statusX
dateY, amtY, statusY
Not all date1/2/3 will have a dateX associated, if so amt1 is 0 (encoded into the vlookup statement, essentially, using isna)
If amt1 is 0, I don't want any particular formatting.
Otherwise, I want the amt1 field to be coloured based on the information in the status column. There are 4 possible statuses (red, yellow, green, blue). All fields which are non-zero will be coloured one of these four colours. No fields which are zero will be.
Googling around, I see things like use VB to get beyond limit of 4 statuses and select case in VB and use vlookup in VB but I am not entirely clear how to put these all together.
The colours have to update automatically when I change the status, I cannot need to run a macro.
I have done some programming before. I have never used visual basic.
Response by poster: That add-in is not compatible with my version of Excel. It also has bad reviews.
This is a work computer, so assume that I cannot install anything nor can I pay for anything.
posted by jeather at 8:07 AM on July 22, 2011
This is a work computer, so assume that I cannot install anything nor can I pay for anything.
posted by jeather at 8:07 AM on July 22, 2011
Your first link looks like it should get you most of the way. Modify the Cell range to be the range of the amtX column, then set each of the Cases to be the value ranges to want to be a certain color. Finally, associate the icolor value for each Case with the colors you want them to be shaded (use the Palette Index from that table).
If that doesn't work, there are a couple other methods I've used in the past (built off of recording macros, then modifying the code), but that's been in a more recent version of Excel, so I'm not positive if they'll work in this case.
posted by BevosAngryGhost at 1:07 PM on July 22, 2011
If that doesn't work, there are a couple other methods I've used in the past (built off of recording macros, then modifying the code), but that's been in a more recent version of Excel, so I'm not positive if they'll work in this case.
posted by BevosAngryGhost at 1:07 PM on July 22, 2011
Response by poster: The problem is that I do not want to shade amt1 based on the contents of amt1, I want to shade them based on the contents of statusX, where I figure out what statusX is based on a lookup.
I figured that I can simplify it (for me) a bit and do the lookup before the code, so I can make my question:
I need to conditionally format a1:a20 based on the contents of b1:b20, using more cases than conditional formatting allows. I don't see how I can change that code to do that -- I am sure it is possible, but I have no idea how.
posted by jeather at 1:39 PM on July 22, 2011
I figured that I can simplify it (for me) a bit and do the lookup before the code, so I can make my question:
I need to conditionally format a1:a20 based on the contents of b1:b20, using more cases than conditional formatting allows. I don't see how I can change that code to do that -- I am sure it is possible, but I have no idea how.
posted by jeather at 1:39 PM on July 22, 2011
Try this:
Target(Offset 0, -1).Interior.ColorIndex = icolor
posted by Balonious Assault at 2:18 PM on July 22, 2011
Target(Offset 0, -1).Interior.ColorIndex = icolor
posted by Balonious Assault at 2:18 PM on July 22, 2011
Any luck with it?
Another thought... you could probably use regular conditional formatting in column A for the zero value/no format. That way your VBA code only has to check the values in column B for the actual color shading. If there is ever a case where there is a value in column B that would otherwise shade the corresponding cell in column A a different color, I believe the conditional formatting in column A will override the VBA code telling it to shade that cell a different color (but I don't have Excel 2000 to verify that).
posted by Balonious Assault at 7:30 AM on July 23, 2011
Another thought... you could probably use regular conditional formatting in column A for the zero value/no format. That way your VBA code only has to check the values in column B for the actual color shading. If there is ever a case where there is a value in column B that would otherwise shade the corresponding cell in column A a different color, I believe the conditional formatting in column A will override the VBA code telling it to shade that cell a different color (but I don't have Excel 2000 to verify that).
posted by Balonious Assault at 7:30 AM on July 23, 2011
Response by poster: It is the weekend, and I have to try again on Monday when I am back at work. My timing was inopportune. There will never be a value in column B if the value in column A is 0.
It looks like it will work, I hope.
posted by jeather at 9:01 AM on July 23, 2011
It looks like it will work, I hope.
posted by jeather at 9:01 AM on July 23, 2011
Response by poster: I have:
Private Sub StatColourChange(ByVal Target As Range)
Dim icolor As Integer
If Not Intersect(Target, Range("0103:0125")) Is Nothing Then
Select Case Target.Value
Case "white"
icolor = 1
Case "red"
icolor = 2
Case "blue"
icolor = 7
Case "green"
icolor = 3
Case "yellow"
icolor = 43
End Select
Target.Offset(0, -9).Interior.ColorIndex = icolor
End Sub
I am trying to change the colouring of cells f103:f125 based on content in o103:o125 -- that content is one of the five colour names in my case statement. The cases match.
This has exactly zero effect. When I try getting rid of the offset info, it also has no effect on the cells in column O.
posted by jeather at 6:59 AM on July 25, 2011
Private Sub StatColourChange(ByVal Target As Range)
Dim icolor As Integer
If Not Intersect(Target, Range("0103:0125")) Is Nothing Then
Select Case Target.Value
Case "white"
icolor = 1
Case "red"
icolor = 2
Case "blue"
icolor = 7
Case "green"
icolor = 3
Case "yellow"
icolor = 43
End Select
Target.Offset(0, -9).Interior.ColorIndex = icolor
End Sub
I am trying to change the colouring of cells f103:f125 based on content in o103:o125 -- that content is one of the five colour names in my case statement. The cases match.
This has exactly zero effect. When I try getting rid of the offset info, it also has no effect on the cells in column O.
posted by jeather at 6:59 AM on July 25, 2011
It has to be a SelectionChange Sub to fire automatically. Try changing it to "Private Sub Worksheet_SelectionChange(ByVal Target As Range)". (This should be in the VB Editor, on the page for the Sheet you want it to work on. At the top of the editor page, the two fields with the dropdown lists should read "Worksheet" and "SelectionChange".
posted by Balonious Assault at 7:24 AM on July 25, 2011
posted by Balonious Assault at 7:24 AM on July 25, 2011
There also has to be an "End If" before the End Sub (to end the "If Not Intersect" statement).
Additionally, the color indexes look a little suspect to me (these are the colors that work for me in Excel 2003).
To make it a little more intuitive-looking you could use something like this, instead of the Case Select:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("0103:0125")) Is Nothing Then
If Target.Cells.Value = "red" Then Target.Offset(0, -9).Interior.ColorIndex = 3
If Target.Cells.Value = "blue" Then Target.Offset(0, -9).Interior.ColorIndex = 5
If Target.Cells.Value = "green" Then Target.Offset(0, -9).Interior.ColorIndex = 4
If Target.Cells.Value = "yellow" Then Target.Offset(0, -9).Interior.ColorIndex = 6
If Target.Cells.Value = "white" Then Target.Offset(0, -9).Interior.ColorIndex = 0
End If
End Sub
posted by Balonious Assault at 7:54 AM on July 25, 2011
Additionally, the color indexes look a little suspect to me (these are the colors that work for me in Excel 2003).
To make it a little more intuitive-looking you could use something like this, instead of the Case Select:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("0103:0125")) Is Nothing Then
If Target.Cells.Value = "red" Then Target.Offset(0, -9).Interior.ColorIndex = 3
If Target.Cells.Value = "blue" Then Target.Offset(0, -9).Interior.ColorIndex = 5
If Target.Cells.Value = "green" Then Target.Offset(0, -9).Interior.ColorIndex = 4
If Target.Cells.Value = "yellow" Then Target.Offset(0, -9).Interior.ColorIndex = 6
If Target.Cells.Value = "white" Then Target.Offset(0, -9).Interior.ColorIndex = 0
End If
End Sub
posted by Balonious Assault at 7:54 AM on July 25, 2011
Response by poster: It almost works. The problem is that for the colour to update, I have to edit column O (press enter in each cell works), but column O is full of vlookups and will be hidden. Actually, no one should ever be editing either column O or column F, they should be editing the lookup table down elsewhere on the sheet.
How can I get it to update automatically, is it even possible?
I got colour codes from here, in the end, because the earlier page had weird numbers. I wasn't using the common yellow or blue.
posted by jeather at 9:04 AM on July 25, 2011
How can I get it to update automatically, is it even possible?
I got colour codes from here, in the end, because the earlier page had weird numbers. I wasn't using the common yellow or blue.
posted by jeather at 9:04 AM on July 25, 2011
Unfortunately I'm at work now and don't have much time to get into it. My first thought would be to see if incorporating the vlookups into the VBA code would work. It looks like there might be some useful information here.
posted by Balonious Assault at 10:34 AM on July 25, 2011
posted by Balonious Assault at 10:34 AM on July 25, 2011
Okeedokee... try this. It's not the most elegant solution, but I think it might work. It's a brute force approach. When one cell in the range changes, it loops through the entire range to check the values and apply the formatting.
----
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("O103:O126")) Is Nothing Then
Application.ScreenUpdating = False
For Each cell In Range("O103:O126")
If cell.Value = "red" Then cell.Offset(0, -9).Interior.ColorIndex = 3
If cell.Value = "blue" Then cell.Offset(0, -9).Interior.ColorIndex = 5
If cell.Value = "green" Then cell.Offset(0, -9).Interior.ColorIndex = 4
If cell.Value = "yellow" Then cell.Offset(0, -9).Interior.ColorIndex = 6
If cell.Value = "white" Then cell.Offset(0, -9).Interior.ColorIndex = 0
If cell.Value = "" Then cell.Offset(0, -9).Interior.ColorIndex = 0
If cell.Row = 126 Then cell.Offset(0, -9).Interior.ColorIndex = 0
Next cell
Application.ScreenUpdating = True
End If
End Sub
---
The If Target.Count>1 statement at the beginning just prevents it from throwing an error if someone selects more than one cell in that range.
Using 126 instead of 125 for the rows in the two range statements should make it so it will apply the formatting on the last row (125). This is a problem I run into in Excel 2003 anyway but I'm entering the data manually on my test worksheet, not doing vlookups (I've actually never used vlookups, so no guarantees it will work at all). You might play with those numbers to see if it's necessary for what you're doing. If it is necessary, then the If cell.row = 126 statement undoes whatever formatting it may change when it loops through that extra row.
Alternately, and perhaps an even better approach, would be to have it scan the lookup table for changes (the If Not Intersect statement), then run the formatting loop checking the values of cells O103 - O125.
The Application.ScreenUpdating statements just tell it to do its thing behind the scenes and then apply the changes all at once after the code is done running. Depending on the computer you're using it might make it look a little cleaner, preventing some screen flashing/flickering as it loops through the range and applies the shading one cell at a time.
posted by Balonious Assault at 8:18 PM on July 25, 2011
----
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("O103:O126")) Is Nothing Then
Application.ScreenUpdating = False
For Each cell In Range("O103:O126")
If cell.Value = "red" Then cell.Offset(0, -9).Interior.ColorIndex = 3
If cell.Value = "blue" Then cell.Offset(0, -9).Interior.ColorIndex = 5
If cell.Value = "green" Then cell.Offset(0, -9).Interior.ColorIndex = 4
If cell.Value = "yellow" Then cell.Offset(0, -9).Interior.ColorIndex = 6
If cell.Value = "white" Then cell.Offset(0, -9).Interior.ColorIndex = 0
If cell.Value = "" Then cell.Offset(0, -9).Interior.ColorIndex = 0
If cell.Row = 126 Then cell.Offset(0, -9).Interior.ColorIndex = 0
Next cell
Application.ScreenUpdating = True
End If
End Sub
---
The If Target.Count>1 statement at the beginning just prevents it from throwing an error if someone selects more than one cell in that range.
Using 126 instead of 125 for the rows in the two range statements should make it so it will apply the formatting on the last row (125). This is a problem I run into in Excel 2003 anyway but I'm entering the data manually on my test worksheet, not doing vlookups (I've actually never used vlookups, so no guarantees it will work at all). You might play with those numbers to see if it's necessary for what you're doing. If it is necessary, then the If cell.row = 126 statement undoes whatever formatting it may change when it loops through that extra row.
Alternately, and perhaps an even better approach, would be to have it scan the lookup table for changes (the If Not Intersect statement), then run the formatting loop checking the values of cells O103 - O125.
The Application.ScreenUpdating statements just tell it to do its thing behind the scenes and then apply the changes all at once after the code is done running. Depending on the computer you're using it might make it look a little cleaner, preventing some screen flashing/flickering as it loops through the range and applies the shading one cell at a time.
posted by Balonious Assault at 8:18 PM on July 25, 2011
Some of the details of the problem statement need to be quite a bit clearer. It would help if, in describing the problem, you could separate "what I need", "what is intrinsic to the architecture of the problem", and "what I have tried/am trying so far".
Also, if you would be as explicit as possible about the intrinsic aspects (i.e. specific ranges, or at least names of ranges.)
Initially you depicted two columns of data, dates and amounts, arranged in sequential columns. You said you wanted to color amounts based on conditions related to the dates. Later, you said "I need to conditionally format a1:a20 based on the contents of b1:b20", leading me to believe that the amounts actually precede the dates. Still later, you said you were working with columns F and O, with no mention of A and B. I notice you also changed the offset statement Balonious provided in a manner that seems to reflect this. (-1 gets from B to a; -9 gets from O to F.) This creates confusion as to whether F and O are where the primary data of interest truly lies, or whether they merely contain components of ongoing efforts to solve the problem. Or neither. Or...both. Shouldn't be both. If the parameters of the problem keep changing, it becomes very difficult to develop solutions based on static snapshots of it.
I'd like to help, and I know how to do this in principle. The solution is probably not difficult. But I don't want to spend time working from outdated/misstated information. Please read the following restatement of the problem, correct any misinformation in it, and provide any missing information:
Worksheet architecture
Ranges
a Please provide the maximum dimensions of this table
b Please provide the location of this table
posted by perspicio at 10:51 PM on July 25, 2011
Also, if you would be as explicit as possible about the intrinsic aspects (i.e. specific ranges, or at least names of ranges.)
Initially you depicted two columns of data, dates and amounts, arranged in sequential columns. You said you wanted to color amounts based on conditions related to the dates. Later, you said "I need to conditionally format a1:a20 based on the contents of b1:b20", leading me to believe that the amounts actually precede the dates. Still later, you said you were working with columns F and O, with no mention of A and B. I notice you also changed the offset statement Balonious provided in a manner that seems to reflect this. (-1 gets from B to a; -9 gets from O to F.) This creates confusion as to whether F and O are where the primary data of interest truly lies, or whether they merely contain components of ongoing efforts to solve the problem. Or neither. Or...both. Shouldn't be both. If the parameters of the problem keep changing, it becomes very difficult to develop solutions based on static snapshots of it.
I'd like to help, and I know how to do this in principle. The solution is probably not difficult. But I don't want to spend time working from outdated/misstated information. Please read the following restatement of the problem, correct any misinformation in it, and provide any missing information:
Worksheet architecture
Ranges
- Cells A1:A20 display dates.
- Cells B1:B20 display amounts.
- There exists a table that is 3 x ??a somewhere on your worksheetb that contains, in columnar order,
- lookup dates
- lookup amounts
- lookup statuses (color names) - 4 of them
- Column A data changes automatically, weekly
- Column A data is not changed manually at all
- Column B amounts are populated from the table described above via vlookups that use corresponding column A dates as references
- If a column A date does not have an exact match in the lookup table, the corresponding column B amount is set to zero
- You want an automated, self-updating method of coloring cells B1:B20 according to the status associated with each one in the lookup table.
- If a column B amount equals zero, the cell should not be formatted.
a Please provide the maximum dimensions of this table
b Please provide the location of this table
posted by perspicio at 10:51 PM on July 25, 2011
Response by poster: BA, it is still not working unless I edit/select the cells in column O, which I don't want to do.
Perspicio:
I simplified the locations of everything, but you are correct in your description of the problem.
The dates are actually in column B. The amounts are in column F. The specific rows are currently 103-125, but that will change. The first date is always this Monday, and the next 22 weeks. F uses vlookup based on the lookup table I describe below. I currently also have a column O which does a lookup based on the lookup table and contains the status ("white" if there is no corresponding date in the lookup table). I put this in to try to make things simpler, because I was having a lot of trouble with integrating vlookup in visual basic. Column O does not need to be displayed.
There is a lookup table of no more than 20 rows, starting in D144. D contains the dates (Mondays only), E the amounts, F the statuses. This table will be edited.
It is crucial that the colours update when the table D144:F163 is updated, not when cells F103:F125 are edited.
posted by jeather at 6:22 AM on July 26, 2011
Perspicio:
I simplified the locations of everything, but you are correct in your description of the problem.
The dates are actually in column B. The amounts are in column F. The specific rows are currently 103-125, but that will change. The first date is always this Monday, and the next 22 weeks. F uses vlookup based on the lookup table I describe below. I currently also have a column O which does a lookup based on the lookup table and contains the status ("white" if there is no corresponding date in the lookup table). I put this in to try to make things simpler, because I was having a lot of trouble with integrating vlookup in visual basic. Column O does not need to be displayed.
There is a lookup table of no more than 20 rows, starting in D144. D contains the dates (Mondays only), E the amounts, F the statuses. This table will be edited.
It is crucial that the colours update when the table D144:F163 is updated, not when cells F103:F125 are edited.
posted by jeather at 6:22 AM on July 26, 2011
Thanks for clarifying, jeather. Do I understand correctly then that:
posted by perspicio at 3:13 PM on July 26, 2011
- the dates in B103:B125 are all Mondays, starting with Monday of the current week?
- these dates are set up to update automatically whenever a new week begins (as would be accomplished by putting the formula "=TODAY()-WEEKDAY(TODAY())+2" in B103, "=B103+7" in B104, and sweeping the B104 formula down through B125)?
- you want the formatting of the cells containing the amounts (cells F103:F125) to update when the dates automatically update?
- you also want the formatting to update when the contents of the lookup table (cells D144:F163) are changed manually?
posted by perspicio at 3:13 PM on July 26, 2011
Even before you answer that, I can say that if you are sure that you are going to relocate the non-lookup-table data (column B dates, column F amounts) - especially if you are likely to do so more than once - then creating named ranges for each will simplify the solution such that you will not need to update the code when these changes take place. I personally like to do this in any case, as it makes the code much more intuitive to read.
To do this, you would go to Insert>Name>Define (alt-I-N-D), type a name, i.e. "Dates", select the current range (B103:B125), click Add (or hit alt-A), and repeat the process for the amounts in F103:F125. Consider doing likewise for the lookup table.
This will make it possible for you to refer to the ranges by name in the VBA code, so that you can simply update the named ranges instead of changing every instance of the cell references throughout the code.
If, on the other hand, you don't intend to move these arrays, but rather expect them to grow or shrink "in place" (i.e. with a fixed starting cell - B103, F103, D144), it will probably be beneficial to establish a maximum range size for them to occupy, and hard-format (i.e. with thick borders) them to make it obvious that no other data should ever occupy those areas. In this case, I would still name the ranges, but I would apply the maximal range size in the reference so that I'd never have to go back and redefine them.
posted by perspicio at 3:35 PM on July 26, 2011
To do this, you would go to Insert>Name>Define (alt-I-N-D), type a name, i.e. "Dates", select the current range (B103:B125), click Add (or hit alt-A), and repeat the process for the amounts in F103:F125. Consider doing likewise for the lookup table.
This will make it possible for you to refer to the ranges by name in the VBA code, so that you can simply update the named ranges instead of changing every instance of the cell references throughout the code.
If, on the other hand, you don't intend to move these arrays, but rather expect them to grow or shrink "in place" (i.e. with a fixed starting cell - B103, F103, D144), it will probably be beneficial to establish a maximum range size for them to occupy, and hard-format (i.e. with thick borders) them to make it obvious that no other data should ever occupy those areas. In this case, I would still name the ranges, but I would apply the maximal range size in the reference so that I'd never have to go back and redefine them.
posted by perspicio at 3:35 PM on July 26, 2011
Response by poster: 1+2. The dates are a bit more complex than that for bizarre internal reasons, but yes, they're all Mondays automatically and always (they are based on a different cell which has that Monday's date in it, plus a bunch of +7s, but that date needs to be changed manually for other reasons). There will never be a problem with the dates.
3+4. Yes.
Extra: I have, like, eleventy zillion worksheets in the same spreadshhet (one for each product) that all should have the same formatting on them. All the sheets have all the ranges in the exact same location. If there is any way to make this happen for all the worksheets, that would be cool (but not necessary).
Right now the worksheet -- which I inherited and am trying to make more automatic, and which I know mostly sucks, but I cannot change it for more internal reasons -- has 80+ hidden lines because of how things used to be set up. As soon as I get a few things approved, I am going to delete the stupid hidden lines and everything will move once and permanently. This will happen for all the worksheets in this spreadsheet, so everything will still be in the same locations on each individual sheet.
I will absolutely format the ranges -- that's a really good idea.
posted by jeather at 4:08 PM on July 26, 2011
3+4. Yes.
Extra: I have, like, eleventy zillion worksheets in the same spreadshhet (one for each product) that all should have the same formatting on them. All the sheets have all the ranges in the exact same location. If there is any way to make this happen for all the worksheets, that would be cool (but not necessary).
Right now the worksheet -- which I inherited and am trying to make more automatic, and which I know mostly sucks, but I cannot change it for more internal reasons -- has 80+ hidden lines because of how things used to be set up. As soon as I get a few things approved, I am going to delete the stupid hidden lines and everything will move once and permanently. This will happen for all the worksheets in this spreadsheet, so everything will still be in the same locations on each individual sheet.
I will absolutely format the ranges -- that's a really good idea.
posted by jeather at 4:08 PM on July 26, 2011
jeather, my window of opportunity didn't arrive last night...will attempt to revisit the problem this evening if nobody else has gotten to it by then. (Sorry to have overpromised!)
posted by perspicio at 5:02 AM on July 27, 2011
posted by perspicio at 5:02 AM on July 27, 2011
Best answer: Ok, I decided to blow off work for a bit this morning to get this done. I created a named range called "Lookups" for the lookup table using the method I described earlier. I also changed the colorindex values you posted earlier because they didn't match up to the names on my computer. (Change 'em back, or to whatever you like, of course.)
Here's the code, located in a single worksheet:
There is probably a simple & elegant way to make it apply to every worksheet by using the ThisWorkbook module instead of having to paste it into every sheet...but I haven't looked into that. Some code tweaking may be required besides replacing "Lookups" with an explicit range reference.
Let me know if you have any problems! (But I really will have to wait till this evening to respond.)
posted by perspicio at 6:27 AM on July 27, 2011
Here's the code, located in a single worksheet:
Option ExplicitIf you put the code in many worksheets you should define the lookup table range explicitly within the worksheets instead of using the named range "Lookups". The reason is defined names are constrained to a particular sheet.
Dim Statuscheck As Variant
Dim AmtCell As Range
Dim icolor As String
Dim DateRef As String
Dim LookupRef As Range
Dim DateCell As Range
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("Lookups")) Is Nothing Then
UpdateCells
End If
End Sub
Private Sub UpdateCells()
Set LookupRef = Range("Lookups")
On Error Resume Next
Application.ScreenUpdating = False
For Each AmtCell In Range("Amounts")
DateRef = AmtCell.Offset(0, -4).Address
Set DateCell = Range(DateRef)
Statuscheck = Application.VLookup(DateCell, LookupRef, 3, False)
If IsError(Statuscheck) Or IsEmpty(Statuscheck) Then Statuscheck = "NA"
Select Case Statuscheck
Case "NA"
icolor = 0
Case "white"
icolor = 2
Case "red"
icolor = 3
Case "blue"
icolor = 5
Case "green"
icolor = 4
Case "yellow"
icolor = 44
End Select
If icolor <> 0 Then
AmtCell.Interior.ColorIndex = icolor
Else
AmtCell.Interior.ColorIndex = xlNone
End If
Next
MsgBox ("OK")
Application.ScreenUpdating = True
End Sub
There is probably a simple & elegant way to make it apply to every worksheet by using the ThisWorkbook module instead of having to paste it into every sheet...but I haven't looked into that. Some code tweaking may be required besides replacing "Lookups" with an explicit range reference.
Let me know if you have any problems! (But I really will have to wait till this evening to respond.)
posted by perspicio at 6:27 AM on July 27, 2011
Oh yeah - my code also uses the named range, "Amounts". You can replace that with an explicit range reference as well.
posted by perspicio at 6:28 AM on July 27, 2011
posted by perspicio at 6:28 AM on July 27, 2011
One more thing: You'll also want to add a snippet of code to run UpdateCells when the parent cell that causes all the dates to change is manually changed. Do this by declaring another variable at the top:
posted by perspicio at 6:51 AM on July 27, 2011
Dim bigrange As RangeDefine it on the line before the Intersect check as:
Set bigrange = Application.Union(Range("Lookups"), Range("[whatever the parent cell address is]"))Finally, change the Intersect check line to:
If Not Intersect(Target, bigrange) Is Nothing ThenThat should take care of it.
posted by perspicio at 6:51 AM on July 27, 2011
Response by poster: Wow, perspicio, that's really awesome. I am fairly sure I can do the minor work of either getting it to work on every worksheet or just doing a lot of copy/paste -- we don't add sheets that often.
And now I can use all the sample code you and Balonius wrote for me here and use it to bootstrap my learning of VB with stuff I can actually use.
Thank you both so very much.
posted by jeather at 1:06 PM on July 27, 2011
And now I can use all the sample code you and Balonius wrote for me here and use it to bootstrap my learning of VB with stuff I can actually use.
Thank you both so very much.
posted by jeather at 1:06 PM on July 27, 2011
This thread is closed to new comments.
http://office.microsoft.com/en-us/marketplace/conditional-formatting-EM010216672.aspx?redir=0
posted by FastGorilla at 8:00 AM on July 22, 2011