Copy formulas down using VBA Macro

bassemjohn1

New Member
Joined
Jun 26, 2020
Messages
29
Office Version
  1. 2016
Platform
  1. Windows
Hello guys,

am trying to find a way where I can let my Macro read that: First, I filter for the bank category in column H, then i add 2 formulas in column F and G then copy it down across all bank rows
then i filter for USD-SEK-EUR and add 2 different Formulas in column f and G and copy it down also then clear the filter.

AEA AED 2 Template For July.xls
CDEFGH
1 Credit amount Debit amount Value date Amount bank Recon Amt Entity
2 22,790.99 1-Jul-2022790.99-22790.99Bank
3 6,052.90 1-Jul-20Bank
4 (1,782.48)1-Jul-20Bank
5 (89.12)1-Jul-20Bank
6 10,634.95 2-Jul-20Bank
7 25,000.00 2-Jul-20Bank
8 22,575.00 26-Jul-20Bank
9 36,960.00 26-Jul-20Bank
10 36,725.00 26-Jul-20Bank
11 2,205.00 26-Jul-20Bank
12 (14,827.00)26-Jul-20Bank
13 (47.25)26-Jul-20Bank
14 3,990.00 26-Jul-20Bank
15 (139,274.62)26-Jul-20Bank
16 (15.75)26-Jul-20Bank
17 5,261.55 27-Jul-20Bank
18 (7,000.00)27-Jul-20Bank
19 (6,200.00)27-Jul-20Bank
20 (47.25)27-Jul-20Bank
21 (23,525.70)27-Jul-20Bank
22 (162.75)27-Jul-20Bank
23 (7,281.03)27-Jul-20Bank
24 (162.75)27-Jul-20Bank
25 11,400.03 27-Jul-20Bank
26 11,202.47 27-Jul-20Bank
27 53,340.00 27-Jul-20Bank
28 55,352.40 27-Jul-20Bank
29 (1,158,450.60)27-Jul-20Bank
30 (236.25)27-Jul-20Bank
31 (392,996.98)27-Jul-20Bank
32 (89.25)27-Jul-20Bank
33 (48,496.29)28-Jul-20Bank
34 (47.25)28-Jul-20Bank
35 4,562.30 28-Jul-20Bank
36 24,723.41 28-Jul-20Bank
37 10,325.51 29-Jul-20Bank
38 (162.75)29-Jul-20Bank
39 (5,170,200.00)29-Jul-20Bank
40 (47.25)29-Jul-20Bank
41 (3,500.30)29-Jul-20Bank
42 (70,450.68)29-Jul-20Bank
43 1,600,000.00 29-Jul-20Bank
446,052.907-Jul-20-6052.9-6052.9EUR
4510,634.957-Jul-20EUR
4625,000.007-Jul-20EUR
472,256.647-Jul-20EUR
4822,575.557-Jul-20EUR
4947,462.637-Jul-20EUR
506,301.497-Jul-20EUR
5114,674.287-Jul-20EUR
5246,513.467-Jul-20EUR
53-975.0023-Jul-20EUR
54-70,000.009-Jul-20EUR
55-297.009-Jul-20EUR
56-2,750.0014-Jul-20EUR
57-3,370.0022-Jul-20EUR
58-4,720.0022-Jul-20EUR
59-5,291.3822-Jul-20EUR
60-48,496.2926-Jul-20EUR
61-7,000.0026-Jul-20EUR
62-392,996.9826-Jul-20EUR
63-1,158,450.6026-Jul-20EUR
64-7,281.0327-Jul-20EUR
65-23,525.7027-Jul-20EUR
66-3,500.3029-Jul-20EUR
67-1,769.2518-Jul-20EUR
68-1,782.481-Jul-20EUR
69-89.121-Jul-20EUR
70-1,500.0021-Jul-20EUR
71-134,917.5722-Jul-20EUR
72-1,522.5023-Jul-20EUR
73-808.5027-Jul-20EUR
74-210.0029-Jul-20EUR
751,600,000.0029-Jul-20EUR
7636,120.0019-Jul-20USD
77-36,206.0023-Jul-20USD
78-7,828.4223-Jul-20USD
79-5,425.5023-Jul-20USD
80-1,767.5923-Jul-20USD
81-414.7523-Jul-20USD
82-20,853.7726-Jul-20USD
83-224,405.6526-Jul-20USD
84-387,013.0226-Jul-20USD
85149,090.0029-Jul-20SEK
86-23,000.007-Jul-20SEK
87-22,546.7923-Jul-20SEK
88-6,664.0023-Jul-20SEK
89-1,239.4015-Jul-20SEK
90-1,000.0015-Jul-20SEK
91-331.5015-Jul-20SEK
92-240.0315-Jul-20SEK
93-3,400.0015-Jul-20SEK
94-7,092.0022-Jul-20SEK
95-14,827.0026-Jul-20SEK
96-139,274.6226-Jul-20SEK
Recon
Cell Formulas
RangeFormula
F2F2=IF(ISBLANK(C2),D2,C2)
G2G2=IFERROR(IF(C2>0,-C2,D2),D2)
F44F44=IF(ISBLANK(C44),-D44,-D44)
G44G44=IF(D44>0,-D44,D44)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
G:GCell ValueduplicatestextYES




the Macro can't seem to understand what i want and always end up not copying the formula down.

so what is the best way to do it while recording macro so macro can easily understand that I filter first then copy formulas down.
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Dan_W

Active Member
Joined
Jul 11, 2018
Messages
297
Office Version
  1. 365
Platform
  1. Windows
What does the code currently look like?

Without the benefit of looking at your code, I have tried to reproduce the problem you described using the macro recorder. The short answer is that the formulas need to be entered only in the visible cells from the filtered data (as below) - from what I can tell, the macro recorder doesn't really know to do that. As an example:

VBA Code:
    Dim rng As Range
    endrow = Range("A1").CurrentRegion.Rows.Count
    Set rng = ActiveSheet.Range("$F$2:$F$" & endrow).SpecialCells(xlCellTypeVisible)
    rng.FormulaR1C1 = "=IF(ISBLANK(RC[-3]),RC[-2],RC[-3])"

Just to make sure - is this the intended result? If so, I'll post the full code.

Book2
CDEFGH
1 Credit amount Debit amount Value date Amount bank Recon Amt Entity
222790.9907/01/202022790.99-22790.99Bank
36052.907/01/20206052.9-6052.9Bank
4-1782.4807/01/2020-1782.48-1782.48Bank
5-89.1207/01/2020-89.12-89.12Bank
610634.9507/02/202010634.95-10634.95Bank
72500007/02/202025000-25000Bank
8225757/26/202022575-22575Bank
9369607/26/202036960-36960Bank
10367257/26/202036725-36725Bank
1122057/26/20202205-2205Bank
12-148277/26/2020-14827-14827Bank
13-47.257/26/2020-47.25-47.25Bank
1439907/26/20203990-3990Bank
15-139274.627/26/2020-139274.62-139274.62Bank
16-15.757/26/2020-15.75-15.75Bank
175261.557/27/20205261.55-5261.55Bank
18-70007/27/2020-7000-7000Bank
19-62007/27/2020-6200-6200Bank
20-47.257/27/2020-47.25-47.25Bank
21-23525.77/27/2020-23525.7-23525.7Bank
22-162.757/27/2020-162.75-162.75Bank
23-7281.037/27/2020-7281.03-7281.03Bank
24-162.757/27/2020-162.75-162.75Bank
2511400.037/27/202011400.03-11400.03Bank
2611202.477/27/202011202.47-11202.47Bank
27533407/27/202053340-53340Bank
2855352.47/27/202055352.4-55352.4Bank
29-1158450.67/27/2020-1158450.6-1158450.6Bank
30-236.257/27/2020-236.25-236.25Bank
31-392996.987/27/2020-392996.98-392996.98Bank
32-89.257/27/2020-89.25-89.25Bank
33-48496.297/28/2020-48496.29-48496.29Bank
34-47.257/28/2020-47.25-47.25Bank
354562.37/28/20204562.3-4562.3Bank
3624723.417/28/202024723.41-24723.41Bank
3710325.517/29/202010325.51-10325.51Bank
38-162.757/29/2020-162.75-162.75Bank
39-51702007/29/2020-5170200-5170200Bank
40-47.257/29/2020-47.25-47.25Bank
41-3500.37/29/2020-3500.3-3500.3Bank
42-70450.687/29/2020-70450.68-70450.68Bank
4316000007/29/20201600000-1600000Bank
446052.907/07/2020-6052.9-6052.9EUR
4510634.9507/07/2020-10634.95-10634.95EUR
462500007/07/2020-25000-25000EUR
472256.6407/07/2020-2256.64-2256.64EUR
4822575.5507/07/2020-22575.55-22575.55EUR
4947462.6307/07/2020-47462.63-47462.63EUR
506301.4907/07/2020-6301.49-6301.49EUR
5114674.2807/07/2020-14674.28-14674.28EUR
5246513.4607/07/2020-46513.46-46513.46EUR
53-9757/23/2020975-975EUR
54-7000007/09/202070000-70000EUR
55-29707/09/2020297-297EUR
56-27507/14/20202750-2750EUR
57-33707/22/20203370-3370EUR
58-47207/22/20204720-4720EUR
59-5291.387/22/20205291.38-5291.38EUR
60-48496.297/26/202048496.29-48496.29EUR
61-70007/26/20207000-7000EUR
62-392996.987/26/2020392996.98-392996.98EUR
63-1158450.67/26/20201158450.6-1158450.6EUR
64-7281.037/27/20207281.03-7281.03EUR
65-23525.77/27/202023525.7-23525.7EUR
66-3500.37/29/20203500.3-3500.3EUR
67-1769.257/18/20201769.25-1769.25EUR
68-1782.4807/01/20201782.48-1782.48EUR
69-89.1207/01/202089.12-89.12EUR
70-15007/21/20201500-1500EUR
71-134917.577/22/2020134917.57-134917.57EUR
72-1522.57/23/20201522.5-1522.5EUR
73-808.57/27/2020808.5-808.5EUR
74-2107/29/2020210-210EUR
7516000007/29/2020-1600000-1600000EUR
76361207/19/2020-36120-36120USD
77-362067/23/202036206-36206USD
78-7828.427/23/20207828.42-7828.42USD
79-5425.57/23/20205425.5-5425.5USD
80-1767.597/23/20201767.59-1767.59USD
81-414.757/23/2020414.75-414.75USD
82-20853.777/26/202020853.77-20853.77USD
83-224405.657/26/2020224405.65-224405.65USD
84-387013.027/26/2020387013.02-387013.02USD
851490907/29/2020-149090-149090SEK
86-2300007/07/202023000-23000SEK
87-22546.797/23/202022546.79-22546.79SEK
88-66647/23/20206664-6664SEK
89-1239.47/15/20201239.4-1239.4SEK
90-10007/15/20201000-1000SEK
91-331.57/15/2020331.5-331.5SEK
92-240.037/15/2020240.03-240.03SEK
93-34007/15/20203400-3400SEK
94-70927/22/20207092-7092SEK
95-148277/26/202014827-14827SEK
96-139274.627/26/2020139274.62-139274.62SEK
TEST DATA
Cell Formulas
RangeFormula
F2:F43F2=IF(ISBLANK(C2),D2,C2)
G2:G43G2=IFERROR(IF(C2>0,-C2,D2),D2)
F44:F96F44=IF(ISBLANK(C44),-D44,-D44)
G44:G96G44=IF(D44>0,-D44,D44)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
G:GCell ValueduplicatestextYES
 

bassemjohn1

New Member
Joined
Jun 26, 2020
Messages
29
Office Version
  1. 2016
Platform
  1. Windows
What does the code currently look like?

Without the benefit of looking at your code, I have tried to reproduce the problem you described using the macro recorder. The short answer is that the formulas need to be entered only in the visible cells from the filtered data (as below) - from what I can tell, the macro recorder doesn't really know to do that. As an example:

VBA Code:
    Dim rng As Range
    endrow = Range("A1").CurrentRegion.Rows.Count
    Set rng = ActiveSheet.Range("$F$2:$F$" & endrow).SpecialCells(xlCellTypeVisible)
    rng.FormulaR1C1 = "=IF(ISBLANK(RC[-3]),RC[-2],RC[-3])"

Just to make sure - is this the intended result? If so, I'll post the full code.

Book2
CDEFGH
1 Credit amount Debit amount Value date Amount bank Recon Amt Entity
222790.9907/01/202022790.99-22790.99Bank
36052.907/01/20206052.9-6052.9Bank
4-1782.4807/01/2020-1782.48-1782.48Bank
5-89.1207/01/2020-89.12-89.12Bank
610634.9507/02/202010634.95-10634.95Bank
72500007/02/202025000-25000Bank
8225757/26/202022575-22575Bank
9369607/26/202036960-36960Bank
10367257/26/202036725-36725Bank
1122057/26/20202205-2205Bank
12-148277/26/2020-14827-14827Bank
13-47.257/26/2020-47.25-47.25Bank
1439907/26/20203990-3990Bank
15-139274.627/26/2020-139274.62-139274.62Bank
16-15.757/26/2020-15.75-15.75Bank
175261.557/27/20205261.55-5261.55Bank
18-70007/27/2020-7000-7000Bank
19-62007/27/2020-6200-6200Bank
20-47.257/27/2020-47.25-47.25Bank
21-23525.77/27/2020-23525.7-23525.7Bank
22-162.757/27/2020-162.75-162.75Bank
23-7281.037/27/2020-7281.03-7281.03Bank
24-162.757/27/2020-162.75-162.75Bank
2511400.037/27/202011400.03-11400.03Bank
2611202.477/27/202011202.47-11202.47Bank
27533407/27/202053340-53340Bank
2855352.47/27/202055352.4-55352.4Bank
29-1158450.67/27/2020-1158450.6-1158450.6Bank
30-236.257/27/2020-236.25-236.25Bank
31-392996.987/27/2020-392996.98-392996.98Bank
32-89.257/27/2020-89.25-89.25Bank
33-48496.297/28/2020-48496.29-48496.29Bank
34-47.257/28/2020-47.25-47.25Bank
354562.37/28/20204562.3-4562.3Bank
3624723.417/28/202024723.41-24723.41Bank
3710325.517/29/202010325.51-10325.51Bank
38-162.757/29/2020-162.75-162.75Bank
39-51702007/29/2020-5170200-5170200Bank
40-47.257/29/2020-47.25-47.25Bank
41-3500.37/29/2020-3500.3-3500.3Bank
42-70450.687/29/2020-70450.68-70450.68Bank
4316000007/29/20201600000-1600000Bank
446052.907/07/2020-6052.9-6052.9EUR
4510634.9507/07/2020-10634.95-10634.95EUR
462500007/07/2020-25000-25000EUR
472256.6407/07/2020-2256.64-2256.64EUR
4822575.5507/07/2020-22575.55-22575.55EUR
4947462.6307/07/2020-47462.63-47462.63EUR
506301.4907/07/2020-6301.49-6301.49EUR
5114674.2807/07/2020-14674.28-14674.28EUR
5246513.4607/07/2020-46513.46-46513.46EUR
53-9757/23/2020975-975EUR
54-7000007/09/202070000-70000EUR
55-29707/09/2020297-297EUR
56-27507/14/20202750-2750EUR
57-33707/22/20203370-3370EUR
58-47207/22/20204720-4720EUR
59-5291.387/22/20205291.38-5291.38EUR
60-48496.297/26/202048496.29-48496.29EUR
61-70007/26/20207000-7000EUR
62-392996.987/26/2020392996.98-392996.98EUR
63-1158450.67/26/20201158450.6-1158450.6EUR
64-7281.037/27/20207281.03-7281.03EUR
65-23525.77/27/202023525.7-23525.7EUR
66-3500.37/29/20203500.3-3500.3EUR
67-1769.257/18/20201769.25-1769.25EUR
68-1782.4807/01/20201782.48-1782.48EUR
69-89.1207/01/202089.12-89.12EUR
70-15007/21/20201500-1500EUR
71-134917.577/22/2020134917.57-134917.57EUR
72-1522.57/23/20201522.5-1522.5EUR
73-808.57/27/2020808.5-808.5EUR
74-2107/29/2020210-210EUR
7516000007/29/2020-1600000-1600000EUR
76361207/19/2020-36120-36120USD
77-362067/23/202036206-36206USD
78-7828.427/23/20207828.42-7828.42USD
79-5425.57/23/20205425.5-5425.5USD
80-1767.597/23/20201767.59-1767.59USD
81-414.757/23/2020414.75-414.75USD
82-20853.777/26/202020853.77-20853.77USD
83-224405.657/26/2020224405.65-224405.65USD
84-387013.027/26/2020387013.02-387013.02USD
851490907/29/2020-149090-149090SEK
86-2300007/07/202023000-23000SEK
87-22546.797/23/202022546.79-22546.79SEK
88-66647/23/20206664-6664SEK
89-1239.47/15/20201239.4-1239.4SEK
90-10007/15/20201000-1000SEK
91-331.57/15/2020331.5-331.5SEK
92-240.037/15/2020240.03-240.03SEK
93-34007/15/20203400-3400SEK
94-70927/22/20207092-7092SEK
95-148277/26/202014827-14827SEK
96-139274.627/26/2020139274.62-139274.62SEK
TEST DATA
Cell Formulas
RangeFormula
F2:F43F2=IF(ISBLANK(C2),D2,C2)
G2:G43G2=IFERROR(IF(C2>0,-C2,D2),D2)
F44:F96F44=IF(ISBLANK(C44),-D44,-D44)
G44:G96G44=IF(D44>0,-D44,D44)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
G:GCell ValueduplicatestextYES

first of all, I'm very thankful for your help sir

second: here's the code: it's working if the sheet of the same size, if not .. the macro gets distracted

VBA Code:
Sub Bank_Recon_Formulas()
'
' Bank_Recon_Formulas Macro
'

'
    Selection.FormulaR1C1 = "=IF(ISBLANK(RC[-3]),RC[-2],RC[-3])"
    Range("G2").Select
    Selection.FormulaR1C1 = "=IFERROR(IF(RC[-4]>0,-RC[-4],RC[-3]),RC[-3])"
    Range("H1").Select
    ActiveSheet.Range("$A$1:$I$529").AutoFilter Field:=8, Criteria1:="Bank"
    Range("F2:G2").Select
    Selection.Copy
    Range("H2").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(0, -2).Range("A1:B1").Select
    Range(Selection, Selection.End(xlUp)).Select
    Application.CutCopyMode = False
    Selection.FillDown
    Selection.End(xlUp).Select
    Range("H1").Select
    ActiveSheet.Range("$A$1:$I$529").AutoFilter Field:=8
    Range("H1").Select
    ActiveSheet.Range("$A$1:$I$529").AutoFilter Field:=8, Criteria1:=Array( _
        "AEA", "AEE", "AEJ"), Operator:=xlFilterValues
    Range("F311").Select
    Selection.FormulaR1C1 = "=IF(ISBLANK(RC[-3]),-RC[-2],-RC[-3])"
    Range("G311").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-3]>0,-RC[-3],RC[-3])"
    Range("F311:G311").Select
    Selection.Copy
    Range("H311").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(0, -2).Range("A1:B1").Select
    Range(Selection, Selection.End(xlUp)).Select
    Application.CutCopyMode = False
    Selection.FillDown
    ActiveCell.Offset(-1, 0).Range("A1").Select
    Selection.End(xlUp).Select
    Range("H1").Select
    ActiveSheet.Range("$A$1:$I$529").AutoFilter Field:=8
    Range("G1").Select
    ActiveWorkbook.Worksheets("Recon").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Recon").AutoFilter.Sort.SortFields.Add Key:=Range( _
        "G1:G529"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Recon").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("F2:G2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Style = "Comma"
    Range("I2").Select
    ActiveCell.FormulaR1C1 = _
        "=COUNTIF(R2C6:RC[-3],RC[-3])>COUNTIF(R2C6:R529C6,-RC[-3])"
    Range("I2").Select
    Selection.Copy
    Range("H2").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(0, 1).Range("A1").Select
    Range(Selection, Selection.End(xlUp)).Select
    Application.CutCopyMode = False
    Selection.FillDown
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    ActiveSheet.Range("$A$1:$I$529").AutoFilter Field:=9, Criteria1:="FALSE"
    Range("F3").Select
    Range(Selection, Selection.End(xlDown)).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 5287936
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range("I1").Select
    ActiveSheet.Range("$A$1:$I$529").AutoFilter Field:=9
End sub
 

Dan_W

Active Member
Joined
Jul 11, 2018
Messages
297
Office Version
  1. 365
Platform
  1. Windows
Great. And is the output I posted in my previous post the correct result?
 

jolivanes

Well-known Member
Joined
Sep 5, 2004
Messages
1,689
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows

ADVERTISEMENT

Re: "the Macro can't seem to understand what i want"
I sure like your humo(u)r. In these trying times, we need some of that.
 

bassemjohn1

New Member
Joined
Jun 26, 2020
Messages
29
Office Version
  1. 2016
Platform
  1. Windows
Re: "the Macro can't seem to understand what i want"
I sure like your humo(u)r. In these trying times, we need some of that.

am glad u are finding your entertainment in an Excel fourm.
 

bassemjohn1

New Member
Joined
Jun 26, 2020
Messages
29
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Great. And is the output I posted in my previous post the correct result?

hello dan, should I add your code alone or tailor it to the existing code ?
 

Dan_W

Active Member
Joined
Jul 11, 2018
Messages
297
Office Version
  1. 365
Platform
  1. Windows
It would be great if you could first answer my question. There is no point in posting the full code if the output is incorrect, is there? So: can you please confirm whether or not the output that I posted above is correct. Thank you.
 

bassemjohn1

New Member
Joined
Jun 26, 2020
Messages
29
Office Version
  1. 2016
Platform
  1. Windows
It would be great if you could first answer my question. There is no point in posting the full code if the output is incorrect, is there? So: can you please confirm whether or not the output that I posted above is correct. Thank you.
sorry am new to VBA and all this stuff, it took me time to understand the code, yes it's want I want
because my problem is when I filter for AEA for example to write my formula, it's on the cell 352 so the macro record it in that specific cell and I want It to be dynamic not in specific cell because my reports vary from month to month.
 

Dan_W

Active Member
Joined
Jul 11, 2018
Messages
297
Office Version
  1. 365
Platform
  1. Windows
Cool. Try this and let me know if it works. Thanks.
VBA Code:
Sub ProcessData()

    Dim rng As Range
    Dim ws As Worksheet
    Set ws = Application.ActiveSheet
    
    endrow = ws.Range("A1").CurrentRegion.Rows.Count
    ws.Range("$A$1:$H$" & endrow).AutoFilter Field:=8, Criteria1:="=Bank"
    Set rng = ws.Range("F2:F" & endrow).SpecialCells(xlCellTypeVisible)
    rng.FormulaR1C1 = "=IF(ISBLANK(RC[-3]),RC[-2],RC[-3])"
    Set rng = ws.Range("G2:G" & endrow).SpecialCells(xlCellTypeVisible)
    rng.FormulaR1C1 = "=IFERROR(IF(RC[-4]>0,-RC[-4],RC[-3]),RC[-3])"
    ws.ShowAllData

    ws.Range("$A$1:$H$" & endrow).AutoFilter Field:=8, Criteria1:=Array("EUR", "SEK", "USD"), Operator:=xlFilterValues
    Set rng = ws.Range("F2:F" & endrow).SpecialCells(xlCellTypeVisible)
    rng.FormulaR1C1 = "=IF(ISBLANK(RC[-3]),-RC[-2],-RC[-2])"
    Set rng = ws.Range("G2:G" & endrow).SpecialCells(xlCellTypeVisible)
    rng.FormulaR1C1 = "=IF(RC[-3]>0,-RC[-3],RC[-3])"
     

    ws.ShowAllData
    AddConditionalFormatting ws
End Sub
Sub AddConditionalFormatting(ws As Worksheet)
    Dim HighlightDuplicates As Object
    
    ws.Range("G:G").FormatConditions.AddUniqueValues
    Set HighlightDuplicates = ws.Range("G:G").FormatConditions(1)
    
    With HighlightDuplicates
        .SetFirstPriority
        .DupeUnique = xlDuplicate
        .Font.Color = -16383844
        .Interior.Color = 13551615
        .StopIfTrue = False
    End With
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,232
Messages
5,594,956
Members
413,954
Latest member
mrsandy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top