Conditional formatting "Applies to" range changes after using macro

[pdmm]

Hi Everyone

I have a worksheet where I use a macro to pull out data from a standard report.
Also, I have a reset macro, which clears the sheet for me, so I can start over if I want to.

Now I would like to use conditionally formatting to colour code some select cells that contain some specific words.
I am able to use a match formula to make that happen, however (and this is where my problem starts)...the specified area for conditionally formatting changes after I reset my worksheet. Why does this happen?
It's worth noting that the conditonally formatting doesn't delete when resetting, but the "Applies To" range is not the one I originally set.

I have tried countless of things and nothing works. Hence I am now reaching out to the experts in this forum for advice.

Hope someone is able to help.

Best,
Daniel

[xlnitwit]

Hi,

What is the code that resets the worksheet?

[pdmm]

Hi,

What is the code that resets the worksheet?

It looks like this (I didn't write it myself)

Application.ScreenUpdating = False
Range("B14").Select
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 29
ActiveWindow.ScrollRow = 46
ActiveWindow.ScrollRow = 59
ActiveWindow.ScrollRow = 77
ActiveWindow.ScrollRow = 99
ActiveWindow.ScrollRow = 122
ActiveWindow.ScrollRow = 145
ActiveWindow.ScrollRow = 175
ActiveWindow.ScrollRow = 205
ActiveWindow.ScrollRow = 240
ActiveWindow.ScrollRow = 293
ActiveWindow.ScrollRow = 348
ActiveWindow.ScrollRow = 404
ActiveWindow.ScrollRow = 462
ActiveWindow.ScrollRow = 500
ActiveWindow.ScrollRow = 540
ActiveWindow.ScrollRow = 593
ActiveWindow.ScrollRow = 630
ActiveWindow.ScrollRow = 671
ActiveWindow.ScrollRow = 709
ActiveWindow.ScrollRow = 751
ActiveWindow.ScrollRow = 784
ActiveWindow.ScrollRow = 822
ActiveWindow.ScrollRow = 860
ActiveWindow.ScrollRow = 902
ActiveWindow.ScrollRow = 940
ActiveWindow.ScrollRow = 970
ActiveWindow.ScrollRow = 1011
ActiveWindow.ScrollRow = 1043
ActiveWindow.ScrollRow = 1076
ActiveWindow.ScrollRow = 1111
ActiveWindow.ScrollRow = 1137
ActiveWindow.ScrollRow = 1169
ActiveWindow.ScrollRow = 1197
ActiveWindow.ScrollRow = 1222
ActiveWindow.ScrollRow = 1250
ActiveWindow.ScrollRow = 1278
ActiveWindow.ScrollRow = 1303
ActiveWindow.ScrollRow = 1333
ActiveWindow.ScrollRow = 1361
ActiveWindow.ScrollRow = 1388
ActiveWindow.ScrollRow = 1411
ActiveWindow.ScrollRow = 1426
ActiveWindow.ScrollRow = 1441
ActiveWindow.ScrollRow = 1456
ActiveWindow.ScrollRow = 1464
ActiveWindow.ScrollRow = 1472
ActiveWindow.ScrollRow = 1479
ActiveWindow.ScrollRow = 1484
ActiveWindow.ScrollRow = 1492
ActiveWindow.ScrollRow = 1504
ActiveWindow.ScrollRow = 1514
ActiveWindow.ScrollRow = 1527
ActiveWindow.ScrollRow = 1534
ActiveWindow.ScrollRow = 1545
ActiveWindow.ScrollRow = 1555
ActiveWindow.ScrollRow = 1562
ActiveWindow.ScrollRow = 1572
ActiveWindow.ScrollRow = 1577
ActiveWindow.ScrollRow = 1582
ActiveWindow.ScrollRow = 1587
ActiveWindow.ScrollRow = 1595
ActiveWindow.ScrollRow = 1602
ActiveWindow.ScrollRow = 1613
ActiveWindow.ScrollRow = 1625
ActiveWindow.ScrollRow = 1635
ActiveWindow.ScrollRow = 1643
ActiveWindow.ScrollRow = 1655
ActiveWindow.ScrollRow = 1665
ActiveWindow.ScrollRow = 1681
ActiveWindow.ScrollRow = 1701
ActiveWindow.ScrollRow = 1718
ActiveWindow.ScrollRow = 1741
ActiveWindow.ScrollRow = 1759
ActiveWindow.ScrollRow = 1781
ActiveWindow.ScrollRow = 1794
ActiveWindow.ScrollRow = 1814
ActiveWindow.ScrollRow = 1832
ActiveWindow.ScrollRow = 1852
ActiveWindow.ScrollRow = 1874
ActiveWindow.ScrollRow = 1895
ActiveWindow.ScrollRow = 1915
ActiveWindow.ScrollRow = 1932
ActiveWindow.ScrollRow = 1950
ActiveWindow.ScrollRow = 1960
Range("B14:R2004").Select
Selection.ClearContents
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 21
ActiveWindow.ScrollRow = 41
ActiveWindow.ScrollRow = 77
ActiveWindow.ScrollRow = 119
ActiveWindow.ScrollRow = 165
ActiveWindow.ScrollRow = 228
ActiveWindow.ScrollRow = 283
ActiveWindow.ScrollRow = 356
ActiveWindow.ScrollRow = 422
ActiveWindow.ScrollRow = 477
ActiveWindow.ScrollRow = 542
ActiveWindow.ScrollRow = 590
ActiveWindow.ScrollRow = 648
ActiveWindow.ScrollRow = 711
ActiveWindow.ScrollRow = 794
ActiveWindow.ScrollRow = 875
ActiveWindow.ScrollRow = 955
ActiveWindow.ScrollRow = 1031
ActiveWindow.ScrollRow = 1104
ActiveWindow.ScrollRow = 1162
ActiveWindow.ScrollRow = 1225
ActiveWindow.ScrollRow = 1278
ActiveWindow.ScrollRow = 1336
ActiveWindow.ScrollRow = 1376
ActiveWindow.ScrollRow = 1421
ActiveWindow.ScrollRow = 1451
ActiveWindow.ScrollRow = 1479
ActiveWindow.ScrollRow = 1507
ActiveWindow.ScrollRow = 1534
ActiveWindow.ScrollRow = 1555
ActiveWindow.ScrollRow = 1575
ActiveWindow.ScrollRow = 1595
ActiveWindow.ScrollRow = 1613
ActiveWindow.ScrollRow = 1633
ActiveWindow.ScrollRow = 1650
ActiveWindow.ScrollRow = 1670
ActiveWindow.ScrollRow = 1686
ActiveWindow.ScrollRow = 1698
ActiveWindow.ScrollRow = 1716
ActiveWindow.ScrollRow = 1728
ActiveWindow.ScrollRow = 1741
ActiveWindow.ScrollRow = 1748
ActiveWindow.ScrollRow = 1761
ActiveWindow.ScrollRow = 1774
ActiveWindow.ScrollRow = 1786
ActiveWindow.ScrollRow = 1801
ActiveWindow.ScrollRow = 1811
ActiveWindow.ScrollRow = 1829
ActiveWindow.ScrollRow = 1834
ActiveWindow.ScrollRow = 1842
ActiveWindow.ScrollRow = 1844
ActiveWindow.ScrollRow = 1849
ActiveWindow.ScrollRow = 1852
ActiveWindow.ScrollRow = 1854
ActiveWindow.ScrollRow = 1857
ActiveWindow.ScrollRow = 1862
ActiveWindow.ScrollRow = 1864
ActiveWindow.ScrollRow = 1867
ActiveWindow.ScrollRow = 1869
ActiveWindow.ScrollRow = 1872
ActiveWindow.ScrollRow = 1877
ActiveWindow.ScrollRow = 1882
ActiveWindow.ScrollRow = 1889
ActiveWindow.ScrollRow = 1895
ActiveWindow.ScrollRow = 1907
ActiveWindow.ScrollRow = 1920
ActiveWindow.ScrollRow = 1935
ActiveWindow.ScrollRow = 1947
ActiveWindow.ScrollRow = 1960
Range("B2007:R2007").Select
Selection.Copy
ActiveWindow.ScrollRow = 1955
ActiveWindow.ScrollRow = 1942
ActiveWindow.ScrollRow = 1927
ActiveWindow.ScrollRow = 1912
ActiveWindow.ScrollRow = 1887
ActiveWindow.ScrollRow = 1852
ActiveWindow.ScrollRow = 1816
ActiveWindow.ScrollRow = 1781
ActiveWindow.ScrollRow = 1743
ActiveWindow.ScrollRow = 1688
ActiveWindow.ScrollRow = 1630
ActiveWindow.ScrollRow = 1534
ActiveWindow.ScrollRow = 1451
ActiveWindow.ScrollRow = 1371
ActiveWindow.ScrollRow = 1283
ActiveWindow.ScrollRow = 1187
ActiveWindow.ScrollRow = 1099
ActiveWindow.ScrollRow = 1018
ActiveWindow.ScrollRow = 925
ActiveWindow.ScrollRow = 845
ActiveWindow.ScrollRow = 777
ActiveWindow.ScrollRow = 696
ActiveWindow.ScrollRow = 643
ActiveWindow.ScrollRow = 590
ActiveWindow.ScrollRow = 542
ActiveWindow.ScrollRow = 502
ActiveWindow.ScrollRow = 467
ActiveWindow.ScrollRow = 437
ActiveWindow.ScrollRow = 399
ActiveWindow.ScrollRow = 369
ActiveWindow.ScrollRow = 341
ActiveWindow.ScrollRow = 316
ActiveWindow.ScrollRow = 288
ActiveWindow.ScrollRow = 270
ActiveWindow.ScrollRow = 250
ActiveWindow.ScrollRow = 228
ActiveWindow.ScrollRow = 197
ActiveWindow.ScrollRow = 177
ActiveWindow.ScrollRow = 152
ActiveWindow.ScrollRow = 132
ActiveWindow.ScrollRow = 107
ActiveWindow.ScrollRow = 79
ActiveWindow.ScrollRow = 54
ActiveWindow.ScrollRow = 29
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 1
Range("B14").Select
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 41
ActiveWindow.ScrollRow = 64
ActiveWindow.ScrollRow = 94
ActiveWindow.ScrollRow = 129
ActiveWindow.ScrollRow = 185
ActiveWindow.ScrollRow = 258
ActiveWindow.ScrollRow = 343
ActiveWindow.ScrollRow = 409
ActiveWindow.ScrollRow = 492
ActiveWindow.ScrollRow = 550
ActiveWindow.ScrollRow = 605
ActiveWindow.ScrollRow = 676
ActiveWindow.ScrollRow = 739
ActiveWindow.ScrollRow = 809
ActiveWindow.ScrollRow = 872
ActiveWindow.ScrollRow = 978
ActiveWindow.ScrollRow = 1061
ActiveWindow.ScrollRow = 1147
ActiveWindow.ScrollRow = 1230
ActiveWindow.ScrollRow = 1320
ActiveWindow.ScrollRow = 1383
ActiveWindow.ScrollRow = 1446
ActiveWindow.ScrollRow = 1482
ActiveWindow.ScrollRow = 1519
ActiveWindow.ScrollRow = 1547
ActiveWindow.ScrollRow = 1575
ActiveWindow.ScrollRow = 1602
ActiveWindow.ScrollRow = 1620
ActiveWindow.ScrollRow = 1640
ActiveWindow.ScrollRow = 1660
ActiveWindow.ScrollRow = 1673
ActiveWindow.ScrollRow = 1691
ActiveWindow.ScrollRow = 1706
ActiveWindow.ScrollRow = 1721
ActiveWindow.ScrollRow = 1738
ActiveWindow.ScrollRow = 1748
ActiveWindow.ScrollRow = 1759
ActiveWindow.ScrollRow = 1761
ActiveWindow.ScrollRow = 1769
ActiveWindow.ScrollRow = 1774
ActiveWindow.ScrollRow = 1779
ActiveWindow.ScrollRow = 1781
ActiveWindow.ScrollRow = 1791
ActiveWindow.ScrollRow = 1804
ActiveWindow.ScrollRow = 1822
ActiveWindow.ScrollRow = 1839
ActiveWindow.ScrollRow = 1854
ActiveWindow.ScrollRow = 1864
ActiveWindow.ScrollRow = 1874
ActiveWindow.ScrollRow = 1884
ActiveWindow.ScrollRow = 1892
ActiveWindow.ScrollRow = 1902
ActiveWindow.ScrollRow = 1912
ActiveWindow.ScrollRow = 1920
ActiveWindow.ScrollRow = 1930
ActiveWindow.ScrollRow = 1940
ActiveWindow.ScrollRow = 1950
ActiveWindow.ScrollRow = 1957
ActiveWindow.ScrollRow = 1960
Range("B14:R2004").Select
Selection.PasteSpecial Paste:=xlPasteFormulasAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Application.ScreenUpdating = True
Range("J3").Select
Worksheets("FRA").Columns(19).ClearContents

[xlnitwit]

Please use code tags when posting code!

You can actually replace all of that code with this

Please Login or Register  to view this content.

which will not overwrite any existing conditional formatting.

[pdmm]

Please use code tags when posting code!

You can actually replace all of that code with this

Please Login or Register  to view this content.

which will not overwrite any existing conditional formatting.

Sorry about the posting code. Will pay attention next time.

I'll give it a try! Thank you for your help! Really appreciate it

[pdmm]

I tried it out and unfortunately it still happens. The range adjust to where the data is, however if my data range changes (more or less rows) the conditional formatting doesn't update any more

[xlnitwit]

That code doesn't change the conditional formatting at all so I don't see how it could still be changing the AppliesTo range. Can you post a workbook and explain exactly what it is that you want/don't want to happen?