• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Conditional Formatting aggravation II

Eloise T

Eloise T

Active Member
My Conditional Formatting looks like the below picture, although there are 16 more Rules in addition to what is in the picture below; however, they are all similar in that the Rules are looking for a particular cell value and are applied to: =$C$5:$C$20005
upload_2017-7-6_12-10-44.png

I am currently only using rows up to 19,101, but I add over 100 new rows each week.

My conundrum is the “Applies to” changes/updates itself (without my permission :eek:) to something like:
upload_2017-7-6_12-14-32.png

=$D$19025,$C$19025:$C$19051,$C$19062:$C$19101 …and misses the next week’s new data when I add it because the new data has exceeded row 19101.

Each week and I have to reset the “Applies to” back to: =$C$5:$C$20005 so it will “find” the new data.

Is there a way to prevent the "Applies to" from having a mind of it's own and changing the "Applies to" to what it wants and not what I want?
 

Attachments

  • upload_2017-7-6_12-9-58.png
    upload_2017-7-6_12-9-58.png
    129.1 KB · Views: 4
Hi, Eloise T!
That happens when you move/delete rows with CF. If you just copy then CF doesn't deploy its creativity.
Regards!
 
:eek: That's a huge range to apply CF to.

Personally, I'm not huge fan of CF applied to data range (or even cell format other than date & basic number format). With increase in applied CF, workbook performance can take a severe hit.

I tend to use it only in dashboard level for few select ranges.
 
Eloise T said:
Each week and I have to reset the “Applies to” back to: =$C$5:$C$20005 so it will “find” the new data.
Click to expand...
I agree with Chihiro regarding CF applied to a large data range being likely to be resource hungry, but if you must have it, do the following:
Record a macro of you resetting the applies to to say the first 2 rules.
Stop recording.
I expect you'll get bags of code. (It expecting it will record wiping all the CF and re-applying all the CF)
Post it all here and it'll be tweakable.
 
p45cal said:
I agree with Chihiro regarding CF applied to a large data range being likely to be resource hungry, but if you must have it, do the following:
Record a macro of you resetting the applies to to say the first 2 rules.
Stop recording.
I expect you'll get bags of code. (It expecting it will record wiping all the CF and re-applying all the CF)
Post it all here and it'll be tweakable.
Click to expand...

I'm not sure why I didn't get "a bag full of code" but the following is all that was generated...including the comments. I clicked on Developer tab, Record Macro, Home tab, Conditional Formating, Manage Rules..., down arrow, This Worksheet, clicked on first rule, hit Delete key 21 times, clicked Apply, OK, Developer tab, and Stop Recording.

Sub ConditionalFormatMacro()
'
' ConditionalFormatMacro Macro
'

'
End Sub
 
I've just tested this (Excel 2010). While if I did as you did and just deleted the rules, I got a blank macro. But when I did what I asked you to do:Record a macro of you resetting the applies to to say the first 2 rules.
that is, just adjust the ranges that the first 2 rules apply to from the likes of:
=$D$19025,$C$19025:$C$19051,$C$19062:$C$19101
to the likes of:
=$C$5:$C$20005
I got a lot of code, one of the first lines being:
Cells.FormatConditions.Delete
which deletes all conditional formatting on the sheet. What followed was the code to reinstate all the conditional formatting.
Could you try again?
 
p45cal said:
I've just tested this (Excel 2010). While if I did as you did and just deleted the rules, I got a blank macro. But when I did what I asked you to do:Record a macro of you resetting the applies to to say the first 2 rules.
that is, just adjust the ranges that the first 2 rules apply to from the likes of:
=$D$19025,$C$19025:$C$19051,$C$19062:$C$19101
to the likes of:
=$C$5:$C$20005
I got a lot of code, one of the first lines being:
Cells.FormatConditions.Delete
which deletes all conditional formatting on the sheet. What followed was the code to reinstate all the conditional formatting.
Could you try again?
Click to expand...
I will try again.
 
p45cal said:
I've just tested this (Excel 2010). While if I did as you did and just deleted the rules, I got a blank macro. But when I did what I asked you to do:Record a macro of you resetting the applies to to say the first 2 rules.
that is, just adjust the ranges that the first 2 rules apply to from the likes of:
=$D$19025,$C$19025:$C$19051,$C$19062:$C$19101
to the likes of:
=$C$5:$C$20005
I got a lot of code, one of the first lines being:
Cells.FormatConditions.Delete
which deletes all conditional formatting on the sheet. What followed was the code to reinstate all the conditional formatting.
Could you try again?
Click to expand...


I apologize for not following your request exactly. I should have re-read it twice.

I got more than just a bag, I got the whole grocery store!
It created more than 10,000 lines of code which this forum will not allow so I uploaded it as a MS Word file.
 

Attachments

  • Chandoo - CF Macro CODE.docx
    14.2 KB · Views: 2
Wow.
I ran your code, and for some reason most of the time it's producing conditional formatting in one cell only. So it doesn't look as if this way is going to work.
After some research I find that there could be a cleaner approach, namely a FormatCondition.ModifyAppliesToRange method
So what I'd like you to do now is to to run a little macro twice; once on a sheet where the conditional format's all to cock, and a second time after you have manually put the conditional formats ALL correct. This will show me (a) where things have a tendency to go, and (b) where things should be.
This is the macro (it runs on the active sheet):
Code: 
Sub blah()
On Error Resume Next
For Each fc In Cells.FormatConditions
  Debug.Print "Applies to: " & fc.AppliesTo.Address,
  Debug.Print "Priority: " & fc.Priority,
  Debug.Print "Formula1: " & fc.Formula1,
  Debug.Print "Text: " & fc.Text,
  Debug.Print
Next fc
End Sub
It produces stuff like:
PHP: 
Applies to: $K$19099  Priority: 1  Formula1: =NOT(ISERROR(SEARCH("PNL70",K19099)))  Text: PNL70
Applies to: $K$19099  Priority: 2  Formula1: =NOT(ISERROR(SEARCH("PNC70",K19099)))  Text: PNC70
Applies to: $K$19099  Priority: 3  Formula1: =NOT(ISERROR(SEARCH("PNR70",K19099)))  Text: PNR70
in the Immediate Pane (Ctrl + G in the vbe if you can't see that pane) of the Visual basic editor.
Could you copy and paste the 50-odd lines (you have some 23 CFs?) that this macro produces here?

(I'm assuming you don't want to post your actual Excel workbook here due to sensitivity issues; if that's not the case, post the workbook here instead of all the above, but duplicate the problem sheet, and have one of them where the CF is all wrong due to your having added data, and one where it's all right.)
 
You must log in or register to reply here.
Back
Top