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.
posted by jeather to Computers & Internet (23 answers total)
There is an add-in that looks like it will solve your problem:
posted by FastGorilla at 8:00 AM on July 22, 2011

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

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

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

Try this:

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

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

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

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

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

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

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

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

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

  1. Cells A1:A20 display dates.
  2. Cells B1:B20 display amounts.
  3. 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
  1. Column A data changes automatically, weekly
  2. Column A data is not changed manually at all
  3. Column B amounts are populated from the table described above via vlookups that use corresponding column A dates as references
  4. If a column A date does not have an exact match in the lookup table, the corresponding column B amount is set to zero
Problem Statement
  1. You want an automated, self-updating method of coloring cells B1:B20 according to the status associated with each one in the lookup table.
  2. 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.


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:
  1. the dates in B103:B125 are all Mondays, starting with Monday of the current week?
  2. 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)?
  3. you want the formatting of the cells containing the amounts (cells F103:F125) to update when the dates automatically update?
  4. you also want the formatting to update when the contents of the lookup table (cells D144:F163) are changed manually?
If that is an accurate description, I think I can whip together a workable solution for you when I have a little time this evening.
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

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

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

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:
Option Explicit
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
    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
            AmtCell.Interior.ColorIndex = xlNone
        End If
    MsgBox ("OK")

Application.ScreenUpdating = True

End Sub
If 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.

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

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:
Dim bigrange As Range
Define 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 Then
That 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

« Older In need of new insights re cross-cultural LDR with...   |   Weird Gmail Issue Newer »
This thread is closed to new comments.