Solved

Convert Conditional Formats to "normal" format and removed Cell Values once converted

Posted on 2011-03-15
39
2,872 Views
Last Modified: 2012-05-11
I have a follow up question to one already places.

I am looking to "replace" the conditional formats with "static" or normal formats based on the condition format results.

In my example column AE dictates if the line is a header or a data line.
If Header, i want to leave to conditional formats alone.

If data i want to "replace" the conditional formats with the resulting formatting and remove the value in the cell.

This is to be applies to only colums F thru W leaving all other conditional formats in tact.  The resulting document will be an "order form" which gives the customer indication of stock level without giving them the actual values.

I cannot figure this out from the previous solutions of converting Conditional Formats to "normal"



Availabilty-Format.xlsm
0
Comment
Question by:Element_Can
  • 17
  • 14
  • 6
  • +1
39 Comments
 
LVL 81

Expert Comment

by:byundt
ID: 35140677
Jason,
This question is enormously easier to answer if you use Excel 2010. That's because a new property was added that returns the display color of the cell.

Which version do you use?

Brad
0
 

Author Comment

by:Element_Can
ID: 35140710
I am using 2007.  
0
 
LVL 81

Expert Comment

by:byundt
ID: 35140826
That's too bad. Here is the code for Excel 2010.
Sub RemoveConditionalFormatting()
Dim cel As Range, rg As Range, rw As Range
Set rg = Intersect(Range("F:W"), ActiveSheet.UsedRange)
For Each rw In rg.Rows
    If LCase(rw.EntireRow.Cells(1, 31)) <> "h" Then 'Check if column AE equals "h"
        For Each cel In rw.Cells
            cel.Interior.ColorIndex = cel.DisplayFormat.Interior.ColorIndex
            cel.Font.ColorIndex = cel.DisplayFormat.Font.ColorIndex
            If cel.FormatConditions.Count > 0 Then cel.FormatConditions.Delete
        Next
    End If
Next
End Sub

Open in new window


I'll try to hunt up the required code for Excel 2007.

Brad
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

Author Comment

by:Element_Can
ID: 35141452
Any help would be greatly appreciated.
0
 
LVL 81

Expert Comment

by:byundt
ID: 35143546
I am getting a fatal error in Excel 2010 when testing the following code--which ought to work in Excel 2007. The error occurs on any cell that uses a value between 1 and 11 criteria. The error occurs in function ConditionalColorGE2003. The first instance is cell F8, the fourth format condition.

I wrote the original code some years ago, and it worked in Excel 2003. dlmille modified it in http:/Q_26860897.html but it sure isn't working here. I added a test for the StopIfTrue property, which was added in Excel 2007. Hitherto, conditional formatting always stopped with the first True test result. I modified it further to actually select the cell before interrogating its properties. This prevented some of the fatal errors, but not all of them.

Brad
Sub RemoveConditionalFormatting()
Dim cel As Range, rg As Range, rw As Range
Set rg = Intersect(Range("F:W"), ActiveSheet.UsedRange)
For Each rw In rg.Rows
    If LCase(rw.EntireRow.Cells(1, 31)) <> "h" Then 'Check if column AE equals "h"
        For Each cel In rw.Cells
            If cel.FormatConditions.Count > 0 Then
                cel.Select
                cel.Interior.ColorIndex = ConditionalColor(cel, "Interior") 'Replace the interior (highlight) color
                cel.Font.ColorIndex = ConditionalColor(cel, "Font") 'Replace the font color
                cel.FormatConditions.Delete 'Delete all the Format Conditions for this cell
            End If
        Next
    End If
Next
End Sub

 
'Source: byundt - http://vbaexpress.com/kb/getarticle.php?kb_id=190
Function ConditionalColor(rg As Range, FormatType As String) As Long
    Select Case CDbl(Application.Version)
    Case Is <= 11
        ConditionalColor = ConditionalColorLT2007(rg, FormatType)
    Case 12
        ConditionalColor = ConditionalColorGE2003(rg, FormatType)
    Case Is >= 14
        If FormatType = "Font" Then ConditionalColor = rg.Cells(1).DisplayFormat.Font.ColorIndex
        If FormatType = "Interior" Then ConditionalColor = rg.Cells(1).DisplayFormat.Font.ColorIndex
    End Select
End Function

Function ConditionalColorGE2003(rg As Range, FormatType As String) As Long
     'Returns the color index (either font or interior) of the first cell in range rg. If no _
    conditional format conditions apply, Then returns the regular color of the cell. _
    FormatType Is either "Font" Or "Interior"
    'modified March 4, 2011 - removed test for "Formula Is" for this application - uncertain as well how this works for Excel 2007 and beyond
    'also stripped "=" from formula on the alternative
    
    Dim cel As Range
    Dim tmp As Variant
    Dim boo As Boolean
    Dim frmla As String, frmlaR1C1 As String, frmlaA1 As String
    Dim i As Long
     
    'Application.Volatile    'This statement required if Conditional Formatting for rg is determined by the _
                                    value of other cells
     
    Set cel = rg.Cells(1, 1)
    Select Case Left(LCase(FormatType), 1)
    Case "f" 'Font color
        ConditionalColorGE2003 = cel.Font.ColorIndex
    Case Else 'Interior or highlight color
        ConditionalColorGE2003 = cel.Interior.ColorIndex
    End Select
     
    If cel.FormatConditions.Count > 0 Then
        'On Error GoTo errhandler
        With cel.FormatConditions
            For i = 1 To .Count 'Loop through the three possible format conditions for each cell
                frmla = .Item(i).Formula1
                If Left(frmla, 1) = "=" And Not IsNumeric(Right(frmla, Len(frmla) - 1)) Then 'Believe this captures the "Formula Is" case
'                If Left(frmla, 1) = "=" Then 'If "Formula Is", then evaluate if it is True 'commented this out - as Excel 2007+ don't work this way
                     'Conditional Formatting is interpreted relative to the active cell. _
                    This cause the wrong results If the formula isn 't restated relative to the cell containing the _
                    Conditional Formatting--hence the workaround using ConvertFormula twice In a row. _
                    If the Function were Not called using a worksheet formula, you could just activate the cell instead.
                    frmlaR1C1 = Application.ConvertFormula(frmla, xlA1, xlR1C1, , ActiveCell)
                    frmlaA1 = Application.ConvertFormula(frmlaR1C1, xlR1C1, xlA1, xlAbsolute, cel)
                    'Debug.Print frmla & " found"
                    boo = Application.Evaluate(frmlaA1)
                Else 'If "Value Is", then identify the type of comparison operator and build comparison formula
                 Select Case .Item(i).Operator
                    Case xlEqual ' = x
                        frmla = cel & "=" & Right(.Item(i).Formula1, Len(.Item(i).Formula1) - 1)
                    Case xlNotEqual ' <> x
                        frmla = cel & "<>" & Right(.Item(i).Formula1, Len(.Item(i).Formula1) - 1)
                    Case xlBetween 'x <= cel <= y
                        frmla = "AND(" & Right(.Item(i).Formula1, Len(.Item(i).Formula1) - 1) & "<=" & cel & "," & cel & "<=" & Right(.Item(i).Formula2, Len(.Item(i).Formula2) - 1) & ")"
                    Case xlNotBetween 'x > cel or cel > y
                        frmla = "OR(" & Right(.Item(i).Formula1, Len(.Item(i).Formula1) - 1) & ">" & cel & "," & cel & ">" & Right(.Item(i).Formula2, Len(.Item(i).Formula2) - 1) & ")"
                    Case xlLess ' < x
                        frmla = cel & "<" & Right(.Item(i).Formula1, Len(.Item(i).Formula1) - 1)
                    Case xlLessEqual ' <= x
                        frmla = cel & "<=" & Right(.Item(i).Formula1, Len(.Item(i).Formula1) - 1)
                    Case xlGreater ' > x
                        frmla = cel & ">" & Right(.Item(i).Formula1, Len(.Item(i).Formula1) - 1)
                    Case xlGreaterEqual ' >= x
                        frmla = cel & ">=" & Right(.Item(i).Formula1, Len(.Item(i).Formula1) - 1)
                 End Select
                    boo = Application.Evaluate(frmla) 'Evaluate the "Value Is" comparison formula
                End If
                 
                If boo And cel.FormatConditions(i).StopIfTrue Then 'If this Format Condition is satisfied
                    On Error Resume Next
                    Select Case Left(LCase(FormatType), 1)
                    Case "f" 'Font color
                        tmp = .Item(i).Font.ColorIndex
                    Case Else 'Interior or highlight color
                        tmp = .Item(i).Interior.ColorIndex
                    End Select
                    If Err = 0 Then ConditionalColorGE2003 = tmp
                    Err.Clear
                    On Error GoTo 0
                    Exit For 'Since Format Condition is satisfied, exit the inner loop
                End If
            Next i
        End With
    End If
errhandler:
'On Error GoTo 0
End Function

Open in new window

0
 
LVL 41

Expert Comment

by:dlmille
ID: 35143894
I have the fix for this and its tied to the evaluate when cel is nul.  To save time, I used byundits initial few lines of code and am debugging now - give me a few mins...

For some reason the interior color of the >11 is not returning correctly from the system upon interrogation...  The color condition function works fine now, however the interior.colorindex of the item being interrogated is incorrect for some reason when there is No Color option for interior color in the conditional format.

Dave
0
 
LVL 41

Expert Comment

by:dlmille
ID: 35144007
The conditional formatting was a bit convoluted (to me :) and I believe precedence was being challenged with the original code developed.  If I rearrange the rules a bit, the color change exercise works.

@Jason - Before I spend more time on this, may I ask how many worksheets you're planning to apply this to?  Just this one (simple solution but really a one-off) or many more (worth the effort to work through the logic further).  And if working further means coming up with a way to optimize the conditional formatting from the start, I assume that's ok as well, yes?

Thanks,

Dave
0
 
LVL 81

Expert Comment

by:byundt
ID: 35144138
Dave,
Thanks for jumping in.

Were you replicating my problem with the value of .Item(i).Formula1 on cell F8 when i=4? I actually couldn't get any of the properties (Type, Operator, Formula1, Formula2) for .Item(4), although I could for those before and in some cells for those after.

Brad
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35144268
Please correct my understanding.

If the value of say AE 6 is "D", then the conditional format has to be deleted in F6:W6?

Sid
0
 
LVL 41

Expert Comment

by:dlmille
ID: 35144315
I was able to get it to function perfectly, with the originally converted code and one minor change, however precedence issues popped up and the colorindex being returned was incorrect.


It would be hard to show if you don't have Excel 2007, but everything works correctly, except for those cell formats which have no fill, they are actually going black and that's how the thing is solving.

I'm thinking of handling this problem in a few passes - once to handle the headers then delete those formats.  Perhaps from that point, the rest will work correctly.

Dave
0
 
LVL 41

Expert Comment

by:dlmille
ID: 35144854
THIS IS A ONE-OFF SOLUTION - NOT FOR GENERAL PURPOSE DUE TO REASONS STATED BELOW:

Ok. Cross your fingers.  Some very good collaboration with Sid, so please split points as appropriate!

Here's what's going on.  There is definately a precedence issue that appears to be confusing Excel (or at least it confuses us!) ;)  Fortunately, we were able to overcome.

First, I started with the originally modified code from my work from a week or so ago that enables colorcondition function to "function" in Excel 2007.  Then, I had to fix one bug associated with a condition where the cell being examined was nul (hard to evaluate >11.7 as opposed to 22>11.7!).  That got through the "obvious" bugs.

After quite a bit of trial/error and debug interrogation, it became apparent that there were precedence issues with the way the conditions were put together that Excel could figure out, but not the original solution we had - for example, coverage for Headers was handled in several different areas, and really didn't even need to be handled within the F:W range.

So, I wanted to examine all the conditions (instead of stopping on the first true condition - original code - or leveraging the StopIfTrue capability of Excel 2007).  Really, Excel was telling me "false" information about the color format condition - for a given item, the formula test for the prime (StopIfTrue) condition was incorrect, while the interior color was black (it was displaying the >11.1 formula and saying that should be black).

As a result, if any condition were true past the black condition, that would be the condition that won out.  After testing, the before/after picture was the same, with conditional formulas eliminated (and then I added the code to delete the values in that range).

The long and short of it is, if your spreadsheets that have this type of formatting are consistent, there's a good chance that this "patch" will do the trick.

Here it is "before changes".  Just run the macro from the Developer's ribbon, or Tools/Macro menu - RemoveConditionalFormattingAndDeleteValues()

See the code, below for that - modified from byundits original:

 
Sub RemoveConditionalFormattingAndDeleteValues()
Dim cel As Range, rg As Range, rw As Range
Dim xMsg As Integer

Application.ScreenUpdating = False

Set rg = Intersect(Range("F:W"), ActiveSheet.UsedRange)
For Each rw In rg.Rows
    If LCase(rw.EntireRow.Cells(1, 31)) <> "h" Then 'Check if column AE equals "h"
        For Each cel In rw.Cells
            If cel.FormatConditions.Count > 0 Then
                'cel.Select
                cel.Interior.ColorIndex = ConditionalColor(cel, "Interior") 'Replace the interior (highlight) color
                cel.Font.ColorIndex = ConditionalColor(cel, "Font") 'Replace the font color
                cel.FormatConditions.Delete 'Delete all the Format Conditions for this cell
                cel.Value = ""
            End If
        Next
    End If
Next
Application.ScreenUpdating = True
xMsg = MsgBox("There's a good chance, your conditional formatting has been removed from F:W on nonHeader rows, and replaced with regular formatting.  Also, values have been removed from this range in NON header areas.", vbOKOnly, "Success?")
End Sub

Open in new window


The big change in the conditional color algorithm which clears the error when cel.value is null and the comparison is NULL < 11.7 is highlighted below:
...

                  End Select
                    On Error Resume Next
                    boo = Application.Evaluate(frmla) 'Evaluate the "Value Is" comparison formula
                    If Err <> 0 Then
                        boo = False
                        Err.Clear
                    End If
                    On Error GoTo 0
                End If

Open in new window



Finally, the place where it "falls thru" such that the last successful TRUE formula wins (rather than first) is here:
If boo Then 'If this Format Condition is satisfied
                    On Error Resume Next
                    Select Case Left(LCase(FormatType), 1)
                    Case "f" 'Font color
                        tmp = .Item(i).Font.ColorIndex
                    Case Else 'Interior or highlight color
                        tmp = .Item(i).Interior.ColorIndex
                    End Select
                    If Err = 0 Then ConditionalColorGE2003 = tmp
                    Err.Clear
                    On Error GoTo 0
                    'Exit For 'Since Format Condition is satisfied, exit the inner loop - ignored in this version due to precedence issues with black overlay handled in redundant areas
                    'stop on first true will not work
                End If

Open in new window

See attached - run the macro and enjoy!  Again, this is a PATCH which works in your circumstance, and highlights even more the fragility of examining conditionalformats in Excel 2007.

I also attached a copy of the spreadsheet AFTER having run the macro that others without Excel 2007 can see, and that you can compare with if you're getting different results (hopefully not!)

Dave  
Availabilty-Format-r2.xlsm
Availabilty-Format-r2-AFTER-MACR.xlsm
0
 

Author Comment

by:Element_Can
ID: 35148354
I've reviewed this but am a little confused as to what i am to do?  Am i to combine these codes or run them one after the other?  WOuld it help to re-write the conditions to eliminate the issues and then just have a code that works past the issues with the conditions?

I've tried a few different things and am kind of lost as to what i am to do to move forward.  Sorry for being a pain in the butt but can i just get a more clear picture of what i am to do to apply this to my sheet.
0
 

Author Comment

by:Element_Can
ID: 35148810
Dave,

To be clear.  This will be applied to several work sheets on a daily basis.  I will attach an example of the RAW data which i copy and paste the formats to to arrive at my initial file.  If you can optimize the conditions and then make the macro even easier i'm all for it.  
This is absolutely not a 1 off request.  THanks,

Feel free to re-write the formats to achieve the same result.  Then i will copy and past the formats onto the file or if you have a way of incorporating the formats into the macro even better.

at the end of the day i'm looking for a file which is formatted like the example with no conditions.



Initial-Data.xlsm
Initial-Data--post-Formats-.xlsm
0
 

Author Comment

by:Element_Can
ID: 35150113
Dave,

I managed to get this running for a small batch of Data but it says " connot complete with the resources availabe.  Please select a smaller amount of Data or another application"  when using this with more than 1000 rows.  Most of my data outputs are between 1000 and 2000 rows so this makes it an issue.  I can onyl assume there is too much going on at once for it too work.

I think perhaps starting from scratch and we writing the conditions might work better or applying the process in multiple runs to do different aspects of the formatting would work.  

Anything further you can do would help.  
0
 

Author Comment

by:Element_Can
ID: 35150182
Last point....i don't care if the conditional formats be removed from the whole document as long as all formats remained.  Would that make it easier?  

I wonder if it would make it easier to just build a macro which creates the formats as well as removed the data from the lined instead of applying conditional formats and then trying to remove them.

Just trying to think of another way of doing it.  If so, start with the RAW DATA above and try and end up with the resulting output.
0
 
LVL 81

Expert Comment

by:byundt
ID: 35150555
Jason,
I realize that you don't have a copy of Excel 2010, but decided to test 3 copies of your most recent sample data (2019 rows) just to see its performance. The Excel 2010 specific code (using DisplayFormat property) processed all the rows of data in 90 seconds on my laptop.

Brad
0
 

Author Comment

by:Element_Can
ID: 35150608
So your saying get a copy of 2010....lol.

Could we not look at this from a different angle?  Could we build a Macro to create the formats required and apply them to the raw data instead.  And then remove the data from F thru X based on it it's a header or not.  Might be an easier solution.
0
 

Author Comment

by:Element_Can
ID: 35150615
Main reason for above comment is that i'm not the only one who will run this so it's unrealistic for me to ask everyone to get 2010.  
0
 
LVL 41

Expert Comment

by:dlmille
ID: 35151010
I'll look into this when I get home this evening, with a larger batch of data as well.  I think we can get through this without throwing the baby out with the bathwater.

Dave
0
 
LVL 41

Expert Comment

by:dlmille
ID: 35151044
PS - you need to validate that what I sent you works - see before and after to CONFIRM that's what you're after.

And while the solution may be repeatable for you, I declared it as a one-off solution (to advise the E-E community) as this can't be picked up out of the knowledgebase in its current state and used, given the logic changes I had to implement to work for your solution.

Dave
0
 
LVL 41

Expert Comment

by:dlmille
ID: 35152908
Jason - can you post one of your 1000+ row files for testing on my end?

Cheers,

Dave
0
 
LVL 41

Expert Comment

by:dlmille
ID: 35152929
Jason - I was curious why you closed this out and then added a B rating when there was commitment on my end to try to make this solution fit for you.

Do you require/request further effort here?  If so, please request attention to reopen this query.  I'm committed to doing more than a partial solution on this.

Dave
0
 

Author Comment

by:Element_Can
ID: 35152960
You are of course correct.  I guess i thought it only sort of worked so i said B.  I should have waited...can i now correct that and have it changed?  You have been more than helpful!
0
 
LVL 41

Expert Comment

by:dlmille
ID: 35152965
Yes you can - go ahead and hit Request Attention to have it reopened. Then, when we think we've exhausted the possibilities and our time, feel free to close and rate as you judge within E-E guidelines, of course.  If the solution works for you that's generally an A - kind of works or not great is B (my interpretation, so apologies - you can get the rules off this website and I can lookup and forward if you need help with that at some point).  So, if we stop here, B is probably right, but I assumed we're not done and hope you hold back for a solution that works on the larger file as well - I'm pulling up your data file, now and will double its size and commence testing.

Dave
0
 

Author Comment

by:Element_Can
ID: 35152977
OK...great.  I will gladly do that and appreciate the above and beyond effort.  I was basically just going to do the same copy and past the data to make a larger file.

I certainly am not against the re-writing of the conditions to make it easier.  Or even a new macro which will do both from the raw data.
0
 

Author Comment

by:Element_Can
ID: 35153033
OK...requested the have it re-opened so keep me posted.  I am not married to the conditions that have been written if there is a better way of doing this. (just wanted to make that clear)
0
 

Author Comment

by:Element_Can
ID: 35153250
So do i need to do anything else to progress this solution?
0
 
LVL 41

Expert Comment

by:dlmille
ID: 35153309
Nope - byundit has reopened and I'll be looking at it, hopefully this evening.  Hang in there!

Dave
0
 
LVL 41

Expert Comment

by:dlmille
ID: 35155075
I have very good news.  This is now NOT A ONE OFF SOLUTION anymore, as I believe a little care and diligence applied, the "fixes" can be picked up from this post and used in other applications.  After assessing the memory error we were having, I was able to trap the error to the Excel 4 Macro "Application.Evaluate" function, which I initially believed had no downside in its use.  I had to think a bit about other alternatives to Evaluate.  In fact, I only recently learned about it with another application I was working on about a month ago (before I discovered it, I was pasting formulas in a sheet range and getting the value back!).  I was actually going down that path again when I thought to use the DEFINED NAMES feature of Excel, which has come a long way, over Excel 2003.

As a result, I created a new function and currently know of no drawbacks of using it over the "Application.Evaluate" function for any use (happy to stand corrected, but its rocking well on this application!).

Here's that code (note the fromMacro variable that use utilized by the calling function to instruct the below function to use defined names or application.evaluate based on whether its being run from a macro or worksheet, respectively):
Function myEvaluate(testStr As String, fromMacro As Boolean) As Boolean
'attempt to do a better job than Application.Evaluate to evaluate these operations, as the Excel 4.0 macro calls (lots of them) create memory errors

    myEvaluate = False
      
    'First, check to see if this is a worksheet function, or is running from a macro
    
    'fall thru with FALSE evaluation on comparisons that don't make sense - e.g., comparing a string with a numeric equality
    On Error Resume Next
    If fromMacro Then 'must be running from a macro so leveraging defined name OK
        ActiveWorkbook.Names.Add Name:="toEvaluate", RefersToR1C1:="=False"
    
        ActiveWorkbook.Names("toEvaluate").RefersTo = "=" & testStr
        
        myEvaluate = [toEvaluate]
        
        ActiveWorkbook.Names("toEvaluate").Delete
    Else 'must be running as a worksheet function, so should be "safer" to run as-is with evaluate, as can't make defined name at this level
        myEvaluate = Application.Evaluate(testStr)
    End If
    
    If Err.Number <> 0 Then
        myEvaluate = False
    End If
    Err.Clear
    On Error GoTo 0
    
End Function

Open in new window

Also, note the reason this is no longer a one off, is that its actually working correctly, its just that Excel is really not interpreting the conditional format correct (a bug either way, if I'm somehow mistaken, lol).

Consider, if you look at cell F10, it has 3 format conditions.  Two of the conditions are true.  The second condition is testing the value being > 11.1, have no background fill.

The first condition (with StopIsTrue set) however, is looking at AE1="h" - and in fact, AE1 does equal "h".  Why Excel didn't make that black (alerting the user to a potential mistake) is a mystery.  So, the conditionalcolor routine is indeed returning the correct result for that evaluation...

In fact, if you delete all the conditions but the black condition, the cell does shade black, again why Excel didn't resolve to that is still a mystery, as this condition is the highest precedence.  The StopIfTrue functionality is really designed for backwards compatibility to Excel 2003, and as the OP hasn't indicated otherwise, it has probably been selected in error as Excel 2007 is the format of the worksheet and the many conditions > 3 indicate that as well...  For Excel 2007+ the number of conditions are limited to memory only and precedence only matters, so it will stop on true at any depth of conditions...

A thought...
The conditional format for setting something black COULD be deleted and re-added "correctly".  The visible results are the same, but the function results from our conditionalcolor routines now work correctly!  As a result, I can let the conditional color routine exit the sub when the first format condition that is true
is achieved (per original design).

However, doing the above deleting and setting back is quite a bit of manual labor or another logic job to "fix" the conditional formats, and we can apply a patch for this particular case to deal with the issue?

Ok - I have created two optional parameters for the conditionalcolor routine.  The first is "firstTrue" - to instruct the routine to stop on first "truth" similar to 2003 design, The default is TRUE for this.  If its set to FALSE, then it evaluates all the conditions, returning the colorindex of the last true evaluation (this is for handling the type of exception we see with the "mystery" above, so in conflict actually the last TRUE will matter).

Here's the driving code:
Function ConditionalColor(rg As Range, FormatType As String, Optional firstTrue As Boolean = True, Optional fromMacro As Boolean = False) As Long

    If Application.Version > 11 Then 'higher version than Excel 2003
        ConditionalColor = ConditionalColorGE2003(rg, FormatType, firstTrue, fromMacro)
    Else
        ConditionalColor = ConditionalColorLT2007(rg, FormatType)
    End If
End Function

Open in new window

 
So, assuming no additional conflict resolution needed for THIS and potentially OTHER applications, the last truth is sooth, regardless...

Again, to be clear, I took this path because the conditionalcolor routine correctly interpreted the final color as black, while visually, its not - but when you delete all conditions except the "prime" condition (condition with higher precedence), the cell IS black...

Conclusion - I think we now have a solution that you can use and it is reuseable for others...  Also, I ran this on 1346 rows and it took 1 minute 25seconds.
Success!For worksheet use, the following would be the logical use:

=ConditionalColor(F10,"Interior",TRUE) - for "normal behavior" and
=ConditionalColor(F10,"Interior",FALSE) - to get the last true value and in anticipation of no conflicts, would get the same result as above.  Given the conflict we're dealing with, its the latter approach I took to provide the solution.

Enjoy,

Dave

PS - let me know if you have any issues!  
Initial-Data--post-Formats-r2.xlsm
0
 
LVL 41

Expert Comment

by:dlmille
ID: 35155229
@Jason - in response to your other questions - yes, reformatting could have made this go easier, but it was a good learning experience on multiple levels and I hate to do things manually (though in the end, sometimes that is easier! :)  Also, until the "world" is on Excel 2010+ there will continue to be a need for this type of interrogation tool to get at conditional formatting formats, so this exercise has been useful on that front as well.

Yes, you could have requested code to be written to color cells based on certain rules, but using native Excel for formatting is a good foundation - I also hate to make unnecessary macros (re: ongoing maintenance issues).

To use the tool, just move the code modules to your desired workbook - it targets the F:W range - and run the one macro.  I also included a couple functions to test the time and report that out at the end, with source points embedded in the code module.

I just pushed the formats you gave me to generate 12,000 rows of data and it completed successfully, in just under 20 minutes...  So processing speed average is fairly consistent at about 1,000 rows per minute.

Thanks to perseverance and also suggestions by byundit and SiddhartRout (contributors on this posting) as they collaborated on several levels with me as I worked through this.

Cheers,

Dave
0
 
LVL 81

Expert Comment

by:byundt
ID: 35155848
Dave,
1) You might investigate the use of the Timer function. It returns the number of milliseconds since midnight, and is a more precise way of timing code execution. This would be in lieu of your TimeString function.
2) I tried running your code in Excel 2010, and got a fatal error on cell H2 format condition 3. This is a "cell value is between x and y" type of criteria. This is in ConditionalColorGE2003. I've now had failures in two different computers on this type of criteria in Excel 2010.
frmla = .Item(i).Formula1         'Excel 2010 can't return any properties for this format condition.
I repeated the test in Excel 2007 and it works. If my experience applies to other Excel 2010 users, you need to trap the application version.
3) My screen name lacks an "i"

Brad
0
 
LVL 41

Expert Comment

by:dlmille
ID: 35157905
byundt (Brad) - I stopped using Timer on apps primarily because I have models that sometimes run around that midnight window and sometimes for up to a week, lol.  I also hate to divide by 60 :).  PS the VBA "Now" function is also accurate to the millisecond and you don't have to worry about what day it is.  This reference challenges that Timer is as accurate:  http://vbadud.blogspot.com/2008/10/excel-vba-timestamp-milliseconds-using.html  But I reverted just to keep this codeshare focused on the OP, and the time shared is more to understand whether one can go for coffee while it runs, lol.

I added the trap for Excel2010 to use the native color results, so this should work for 2010 users as well, now.

Cheers,

Dave
Initial-Data--post-Formats-r2.xlsm
0
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
ID: 35157968
Posted the wrong file....  PS - its the system timer API that link references that can get to milliseconds beyond the time limit the basic Timer function apparently has....  Anyway,

Here's the "final" solution.

Dave
Initial-Data--post-Formats-r2.xlsm
0
 

Author Comment

by:Element_Can
ID: 35158121
Thanks for all the help.  The Files worked like a charm.  IT ran on 2300 lines.  So what is the most appropriate way to close this out?  I want to thank you for the solution.  Much of what was said above is "over my head" but if it helps others than great.  For me, it works great.  

Thanks,
0
 
LVL 41

Expert Comment

by:dlmille
ID: 35158535
Great!  Just accept solution and points etc as normal


Dave
0
 

Author Closing Comment

by:Element_Can
ID: 35159885
Many Thanks.  He went above and beyond to solve this for me.
0
 
LVL 41

Expert Comment

by:dlmille
ID: 35173403
Just for the record.  I just got Excel 2003 up and running on a VM Machine to test this code as well.  Our bug fixing for 2007 was a good thing as there was at least one in the original code.

Its all repaired for 2003 as well...  Just in case anyone references this from the knowledgebase...

Attached are both versions.

Dave
Initial-Data--post-Formats-r3.xlsm
Initial-Data--post-Formats-r3.xls
0
 
LVL 41

Expert Comment

by:dlmille
ID: 35173406
Jason - to be clear, the only change made here was to fix a bug for those running Excel 2003 or earlier.

Dave
0

Featured Post

Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question