More than 3 conditional formats

[kaptep]

I Need to be able to format a large range of cells according to the number that they contain. Basically the cells are numbered 1-12 based on a variety of equations from other data and are updated frequently. I need the cells to be 12 different colors based on the value. Conditional formatting works great but only for 3 conditions. What is the easiest way to expand my conditions? I have read several tips for similar scenarios, but I cannot get any of the code to execute. Please give me some suggestions!!!

 

[parry]

Re: 12 conditions

Right now I need to have 12 conditions. The basic idea is that the color codes reflect phases of a project. The information on what phase each of several project is on is coming from several differnt sources but is summarized by a number 1-12 in these cells. The color formatting is used for presentation purposes to create a visual representation of the schedule and progress for the project.

=IF(ISERROR(N45)=TRUE,O44,N45) is an example of one of the cell formulas. The cells referenced in this equation then refer to vlookups and if statements.

So, I get the "hard work" done by getting the correct number 1-12 in each cell. Then I convert this number to a color format through the macro. But I then change the targeted finish date--or the hours required or a number of other factors that the result is based on--the number in the cell updates correctly, but the color formatting does not. That is where I am stuck.

OK, an oversight by Microsoft in my opinion is that changes to a cell value as a result of a formula are picked up bu Conditional Formatting, but are not considered a change as far as the Worksheet_Change event is concerned. Go figure?

So either we can place the worksheet change based on the source of the change or perhaps use another event like a selection change event.

What I would like to know is which cell you manually type something in that has an effect of what the results will be in your formulas?

Using your above example, =IF(ISERROR(N45)=TRUE,O44,N45), N45 is a lookup of a value and if found you put the value of N45 or if not O44? But the formula in N45 is looking up something, so is what it looks up for changes based on something else?

Could you tell me what range has your cells with the formula and which cells effect this value changing.

 

[kaptep]

Back Finally

Sorry, for not responding. I really appreciate the help. I have had to focus on other projects today. The cells that control all of the changes are c4 to i15 of several sheets. The cells where all of the fomatting needs to be done are currently a1 to z500 of sheet 1. I am going to work on this tonight and will hopefully be able to figure it out. If not, I will be back tomorrow with more questions. Thanks again for all of the willingness to help from each of you.

 

[parry]

So if you manually enter info in range C4:I15 on sheetXXX this in turn effects the range A1:Z500 on Sheet1? That seems a lot of cells that you may enter something in which then effects all the formulas.

A Worksheet_Change event on SheetXXX could be kicked off to format the cells on sheet1 if the change was in cells C4:I15. However, this is not ideal as it means that every cell in A1:Z500 (thats 13000 cells) would have to be interrogated to format the correct colour so Im not sure how fast that would be.

Because of this the criteria for when the format happens (ie the cell changes in C4:I15) would have to be as small a range as possible so the formatting isnt done unnecessarily.

If the Worksheet_Change event ran on changes as a result of a formula then this wouldnt be an issue because you would have this event on Sheet1 (rather than SheetXXX) and it would be very quick as it would only need to check formatting on those cells changed rather than all 13000 each time.

 

[parry]

Hi, you can test this out and see if its too slow or not. It formats cells in the range A1:Z500 on Sheet1 provided the cell in question has a formula inside the cell. this is to at least take cells that are blank or constants out of the equation to speed things up.

Place the code in the sheet which causes the formulas in sheet1 to change. This only does 6 variations of formatting but you should get the idea on adding another 6. Plus it wont do any formatting if you make changes to multiple cells in this sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer, c, Rng As Range, Sh As Worksheet, TRng As Range

'Only do formatting if only one cell was changed in the above range
If Target.Cells.Count > 1 Then Exit Sub

'Which cells to look for a change
Set Rng = Range("C4:I15")

'Sheet where you will update formatting
Set Sh = Worksheets("Sheet1")

'Range where you will update formatting (NB: Only cells with a formula
'are looked at)
Set TRng = Sh.Range("A1:Z500").SpecialCells(xlCellTypeFormulas)

'Loop through every cell in the range and apply formatting
If Not Intersect(Range(Target.Address), Rng) Is Nothing Then
    For Each c In TRng
        Select Case c.Value
        Case 1
            icolor = 6
        Case 2
            icolor = 12
        Case 3
            icolor = 7
        Case 4
            icolor = 53
        Case 5
            icolor = 15
        Case 6
            icolor = 42
        Case Else
            icolor = 0 'no color
        End Select
    c.Interior.ColorIndex = icolor
  Next c
End If

'Free memory
Set Rng = Nothing
Set TRng = Nothing
Set Sh = Nothing

End Sub

 

[kaptep]

I am trying your suggestion and think it will probably work. As you have probably figured out I am very unfamiliar with worksheet events, but I had a thought that might work. I just don't know how to write the code for it. What if I had the formatting update/query based on the event of selecting Sheet 1 (the summary sheet where all the formatting from the other shees is displayed) that way I could make several cahnges to multiple sheets without slowing the system down re-formatting everything and then when I selected on sheet 1 the formatting would all be updated. Even if the update process were a little slow it would work fine because I would only have to wait for it when specifically going to that sheet. Is that possible? Thanks again for your help and patience with my lack of knowledge.

 

[kaptep]

Getting Close!

THANKS! I am getting it to work as long as I change cells in C4:115 on Sheet1, and I love it. It is not working when I change cells in C4:I15 on other sheets. So, I have two questions. First, going back to my last idea is it possible to have the event be the selection of Sheet1? Second, just for my furtehr enlightenment how would I need to change the code to get it to work so that I could change cells in C4:I15 on any sheet and get it to update?

 

[kaptep]

Another Question - colors

That little macro for finding the color of a selected cell was very handy. THANKS! So while I am on this whole topic of formatting a colors let me ask another question that has long bothered me about excel. Is there an easy way to customize colors or at least get more color options than what excel offers standard?

 

[parry]

No worries and dont apologise - the purpose of the board is to be able to ask questions.

Yes, what your asking can certainly be done - see code below.

First, I'll explain a little about these events. Open a new workbook & right click any sheet then select View Code you will be taken to the VBE in the particular sheet object. In the right hand window there are two drop down boxes.

The left one has the option (General) to start with so change this to Worksheet. You will suddenly see a new procedure is created called Worksheet_SelectionChange. If you already had a procedure by this name it would now be in focus so its also a navigational tool if you had loads of procedures and couldnt be bothered scrolling. There is only one of each event per sheet.

If you didnt want this event you simply select the text and press the delete key. The right hand box lists all the events you can have in the object called Worksheet. If you go into the VBE help and type 'Worksheet Object Events' into the Answer Wizard box you will see a page listing all these different events with a brief explanation.

What your asking for could be handled by several events but the likely candidates are SelectionChange or Activate. SelectionChange means whenever you move from one cell to another then the event will run. The Activate event means when you select the sheet then the event will run.

The Activate event seems more appropriate to your situation as you only want the event run when you click the sheet not every time you may select a cell in that sheet. You'll note this event is slightly different because it has no argument like some others. This makes sense as you havent had a chance to do anything with any cell in the sheet yet as youve only just clicked on the sheet.

Place the code in the Sheet1 module

Private Sub Worksheet_Activate()
Dim icolor As Integer, c, Rng As Range

'Range where you will update formatting (NB: Only cells with a formula
'are looked at)
Set Rng = Range("A1:Z500").SpecialCells(xlCellTypeFormulas)

'Loop through every cell in the range and apply formatting
    For Each c In Rng
        Select Case c.Value
        Case 1
            icolor = 6
        Case 2
            icolor = 12
        Case 3
            icolor = 7
        Case 4
            icolor = 53
        Case 5
            icolor = 15
        Case 6
            icolor = 42
        Case Else
            icolor = 0 'no color
        End Select
    c.Interior.ColorIndex = icolor
  Next c

'Free memory
Set Rng = Nothing

End Sub

hth

 

[parry]

Re: Another Question - colors

Is there an easy way to customize colors or at least get more color options than what excel offers standard?

Yes, I believe so. According to the VBE Help there is a property called Colors which enabled you to set the colour palette (56 items) using RGB which is a set of three numbers which combined define what colour it is.

I dont think you need to add the colour to the palette but just use the particular RGB numbers something like this...

Range("a1").Interior.Color = RGB(0, 255, 0)

The question is, how can you browse a colour palette to find these numbers? I cannot see an option in Excel but if you have Word then select Format|Background|More Colors and select the Custom tab you can get these three numbers from there. just move the cursor around to get the colour you want and take a note of the numbers.

hth

 

[kaptep]

EXCELLENT

Thanks a ton! I think that answers all my questions for now. The formatting is working and I will play around with the colors a little later. Now I just need to get back to designing the rest of this project. Thanks to everyone for all of the help. Parry a big thanks to you especially. Hopefully soon I will be at the point I can be back to return the favors and answer another newcomer's questions. Have a great day all!