Solved

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

Posted on 2011-03-15
39
2,710 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 80

Expert Comment

by:byundt
Comment Utility
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
Comment Utility
I am using 2007.  
0
 
LVL 80

Expert Comment

by:byundt
Comment Utility
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
 

Author Comment

by:Element_Can
Comment Utility
Any help would be greatly appreciated.
0
 
LVL 80

Expert Comment

by:byundt
Comment Utility
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
Comment Utility
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
Comment Utility
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 80

Expert Comment

by:byundt
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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 80

Expert Comment

by:byundt
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 41

Expert Comment

by:dlmille
Comment Utility
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
Comment Utility
Jason - can you post one of your 1000+ row files for testing on my end?

Cheers,

Dave
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
So do i need to do anything else to progress this solution?
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
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
Comment Utility
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
Comment Utility
@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 80

Expert Comment

by:byundt
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Great!  Just accept solution and points etc as normal


Dave
0
 

Author Closing Comment

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

Expert Comment

by:dlmille
Comment Utility
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
Comment Utility
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

771 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now