My VBA Code is changing Format but there's nothing in the code for that...

An Quala

An Quala

Board Regular
Joined
Mar 21, 2022
Messages
146
Office Version
  1. 2021
Platform
  1. Windows
Hi, so my VBA Code is adding format to Column D ,H, L, and P and I checked with line by line that here in this starting part it is changing the format, yet no code for changing it, I have formatting code in later part of my code but that is not for column D, H, L or P but only for Y:Y as you can see in my bigger code,

Code causing this format apparently:
VBA Code: 
    Dim c As Range, va, x
     For Each x In Split("Sponsored Products Campaigns|Sponsored Brands Campaigns|Sponsored Display Campaigns", "|")
        Set c = Worksheets(x).UsedRange
        va = c.Value
        Worksheets(x).Cells.NumberFormat = "General"
        c = va
    Next

Full Code:
VBA Code: 
Sub workingp()

    Dim c As Range, va, x
     For Each x In Split("Sponsored Products Campaigns|Sponsored Brands Campaigns|Sponsored Display Campaigns", "|")
        Set c = Worksheets(x).UsedRange
        va = c.Value
        Worksheets(x).Cells.NumberFormat = "General"
        c = va
    Next
   
    Sheets("Sponsored Products Campaigns").Select
   
    Columns("D:G").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
   
    Columns("Y:Y").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

    Range("G2").FormulaR1C1 = "=XLOOKUP(RC[1],R2C8:R" & Cells(Rows.Count, 1).End(xlUp).Row & "C8,R2C10:R" & Cells(Rows.Count, 1).End(xlUp).Row & "C10)"
   
    Range("G2").AutoFill Destination:=Range("G2:G" & Cells(Rows.Count, 1).End(xlUp).Row)
   
        Columns("G:G").Copy
    Columns("G:G").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

With Sheets("Sponsored Products Campaigns").Cells(1).CurrentRegion.Resize(, 49)
    With .Offset(1).Resize(.Rows.Count - 1).Columns(49)
        .Value = .Parent.Evaluate("=IF((" & .Offset(, -47).Address & "<>""Keyword"")*(" & .Offset(, -47).Address & "<>""Product Targeting"")+(" & .Offset(, -30).Address & "<>""enabled"")+(" & .Offset(, -4).Address & "<>""enabled"")+(" & .Offset(, -3).Address & "<>""enabled""),"""",1)")
         On Error Resume Next
        .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
         On Error GoTo 0
        .ClearContents
    End With
End With

    Range("F2").FormulaR1C1 = _
        "=XLOOKUP(RC7,'Control Panel'!R9C4:R" & Cells(Rows.Count, 1).End(xlUp).Row & "C4,'Control Panel'!R9C5:R" & Cells(Rows.Count, 1).End(xlUp).Row & "C5)"
    Range("F2").AutoFill Destination:=Range("F2:F" & Cells(Rows.Count, 1).End(xlUp).Row)
    Sheets("Control Panel").Select
    Range("D2:E2").Copy
    Sheets("Sponsored Products Campaigns").Select
    Range("D2").Select
    ActiveSheet.Paste
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "=IF(RC5<>"""",""update"","""")"
    Range("C2:E2").AutoFill Destination:=Range("C2:E" & Cells(Rows.Count, 1).End(xlUp).Row)
    Range("Y1").NumberFormat = "General"
    Range("Y1").FormulaR1C1 = "Bid Change %"
    Range("Y2").FormulaR1C1 = "=IFERROR((RC4-RC24)/RC24,"""")"
    Range("Y2").Style = "Percent"
    Range("Y2").AutoFill Destination:=Range("Y2:Y" & Cells(Rows.Count, 1).End(xlUp).Row)
    Columns("Y:Y").Select
    Selection.FormatConditions.AddDatabar
    Selection.FormatConditions(Selection.FormatConditions.Count).ShowValue = True
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1)
        .MinPoint.Modify newtype:=xlConditionValueAutomaticMin
        .MaxPoint.Modify newtype:=xlConditionValueAutomaticMax
    End With
    With Selection.FormatConditions(1).BarColor
        .Color = 8700771
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).BarFillType = xlDataBarFillSolid
    Selection.FormatConditions(1).Direction = xlContext
    Selection.FormatConditions(1).NegativeBarFormat.ColorType = xlDataBarColor
    Selection.FormatConditions(1).BarBorder.Type = xlDataBarBorderNone
    Selection.FormatConditions(1).AxisPosition = xlDataBarAxisAutomatic
    With Selection.FormatConditions(1).AxisColor
        .Color = 0
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).NegativeBarFormat.Color
        .Color = 255
        .TintAndShade = 0
    End With
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Columns("C:F").Copy
    Columns("C:F").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("F:G").Delete Shift:=xlToLeft
    Range("D2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Cut
    Range("V2").Select
    ActiveSheet.Paste
    Range("E2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Cut
    Range("Q2").Select
    ActiveSheet.Paste
    Columns("D:E").Delete Shift:=xlToLeft

With Sheets("Sponsored Products Campaigns").Cells(1).CurrentRegion.Resize(, 45)
    With .Offset(1).Resize(.Rows.Count - 1).Columns(45)
        .Value = .Parent.Evaluate("=IF((" & .Offset(, -42).Address & "<>""update""),"""",1)")
         On Error Resume Next
        .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
         On Error GoTo 0
        .ClearContents
    End With
End With

Sheets("Sponsored Brands Campaigns").Select
   
    Columns("D:G").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
   
    Columns("X:X").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

    Range("G2").FormulaR1C1 = "=XLOOKUP(RC[1],R2C8:R" & Cells(Rows.Count, 1).End(xlUp).Row & "C8,R2C10:R" & Cells(Rows.Count, 1).End(xlUp).Row & "C10)"
   
    Range("G2").AutoFill Destination:=Range("G2:G" & Cells(Rows.Count, 1).End(xlUp).Row)
   
        Columns("G:G").Copy
    Columns("G:G").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

With Sheets("Sponsored Brands Campaigns").Cells(1).CurrentRegion.Resize(, 54)
    With .Offset(1).Resize(.Rows.Count - 1).Columns(54)
        .Value = .Parent.Evaluate("=IF((" & .Offset(, -52).Address & "<>""Keyword"")*(" & .Offset(, -52).Address & "<>""Product Targeting"")+(" & .Offset(, -36).Address & "<>""running"")*(" & .Offset(, -36).Address & "<>""other"")+(" & .Offset(, -37).Address & "<>""enabled"")+(" & .Offset(, -3).Address & "<>""enabled""),"""",1)")
         On Error Resume Next
        .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
         On Error GoTo 0
        .ClearContents
    End With
End With
    Range("F2").FormulaR1C1 = _
        "=XLOOKUP(RC7,'Control Panel'!R9C4:R" & Cells(Rows.Count, 1).End(xlUp).Row & "C4,'Control Panel'!R9C5:R" & Cells(Rows.Count, 1).End(xlUp).Row & "C5)"
    Sheets("Control Panel").Select
    Range("D4:E4").Copy
    Sheets("Sponsored Brands Campaigns").Select
    Range("D2").Select
    ActiveSheet.Paste
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "=IF(RC5<>"""",""update"","""")"
    Range("C2:F2").AutoFill Destination:=Range("C2:F157")
    Range("X2").Select
    ActiveCell.FormulaR1C1 = "=IFERROR((RC4-RC23)/RC23,"""")"
    Selection.Style = "Percent"
    Selection.FormatConditions.AddDatabar
    Selection.FormatConditions(Selection.FormatConditions.Count).ShowValue = True
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1)
        .MinPoint.Modify newtype:=xlConditionValueAutomaticMin
        .MaxPoint.Modify newtype:=xlConditionValueAutomaticMax
    End With
    With Selection.FormatConditions(1).BarColor
        .Color = 8700771
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).BarFillType = xlDataBarFillSolid
    Selection.FormatConditions(1).Direction = xlContext
    Selection.FormatConditions(1).NegativeBarFormat.ColorType = xlDataBarColor
    Selection.FormatConditions(1).BarBorder.Type = xlDataBarBorderNone
    Selection.FormatConditions(1).AxisPosition = xlDataBarAxisAutomatic
    With Selection.FormatConditions(1).AxisColor
        .Color = 0
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).NegativeBarFormat.Color
        .Color = 255
        .TintAndShade = 0
    End With
    Range("X1").Select
    Selection.NumberFormat = "General"
    ActiveCell.FormulaR1C1 = "Bid Change %"
    Range("X2").Select
    Selection.AutoFill Destination:=Range("X2:X" & Cells(Rows.Count, 1).End(xlUp).Row)
    Columns("X:X").Copy
    Columns("X:X").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    'Range("F1").Activate
    Columns("C:F").Copy
    Columns("C:F").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("F:G").Delete Shift:=xlToLeft
    Range("D2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Cut
    Range("U2").Select
    ActiveSheet.Paste
    Range("E2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Cut
    Range("O2").Select
    ActiveSheet.Paste
    Columns("D:E").Delete Shift:=xlToLeft
With Sheets("Sponsored Brands Campaigns").Cells(1).CurrentRegion.Resize(, 49)
    With .Offset(1).Resize(.Rows.Count - 1).Columns(49)
        .Value = .Parent.Evaluate("=IF((" & .Offset(, -46).Address & "<>""update""),"""",1)")
         On Error Resume Next
        .SpecialCells(xlCellTypeBlanks).EntireRow.Select
         On Error GoTo 0
        .ClearContents
    End With
End With


End Sub


Working.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAW
1ProductEntityOperationCampaign IdDraft Campaign IdPortfolio IdAd Group Id (Read only)Keyword Id (Read only)Product Targeting Id (Read only)Campaign NameStart DateEnd DateStateCampaign Serving Status (Informational only)Budget TypeBudgetBid OptimizationBid MultiplierBidKeyword TextMatch TypeProduct Targeting ExpressionAd FormatLanding Page URLLanding Page AsinsBrand Entity IdBrand NameBrand Logo Asset IdBrand Logo URLCreative HeadlineCreative ASINsVideo Media IdsCreative TypeImpressionsClicksClick-through RateSpendSalesOrdersUnitsConversion RateAcosCPCROASCampaign Name (Informational only)Campaign State (Informational only)Resolved Product Targeting Expression (Informational only)Landing Page Type (Informational only)
2Sponsored BrandsCampaign1.44339E+17Portfolio1Campaign Name - Dummy20210130enabledrunningdaily1000Manual####productCollectionlanding Page URL - DummyENTITYXVEHEBNLogoBrand LogoJadesASIN 1209270.33%3.8726.29110.1414.72%0.556.79Campaign Name Video - DummyproductList
3Sponsored BrandsKeyword1.44339E+171.44309E+171.44372E+17enabledrunning1.1Keyword - Dummyexact9300.00%000000.00%00Campaign Name Video - Dummyenabled
4Sponsored BrandsKeyword1.44339E+171.44309E+171.44378E+17enabledrunning0.94Keyword - Dummyexact158950.31%2.9700000.00%0.590Campaign Name Video - Dummyenabled
5Sponsored BrandsKeyword1.44339E+171.44309E+171.44349E+17enabledrunning1.03Keyword - Dummyexact700.00%000000.00%00Campaign Name Video - Dummyenabled
6Sponsored BrandsKeyword1.44339E+171.44309E+171.44294E+17enabledrunning0.94Keyword - Dummyexact8111.23%0.426.291111.52%0.465.72Campaign Name Video - Dummyenabled
7Sponsored BrandsKeyword1.44339E+171.44309E+171.44173E+17enabledrunning1Keyword - Dummyexact5400.00%000000.00%00Campaign Name Video - Dummyenabled
8Sponsored BrandsKeyword1.44339E+171.44309E+171.44299E+17draftrunning1Keyword - Dummyexact5411.85%0.500000.00%0.50Campaign Name Video - Dummyenabled
9Sponsored BrandsKeyword1.44339E+171.44309E+171.44357E+17enabledlandingPageNotAvailable0.84Keyword - Dummyexact100.00%000000.00%00Campaign Name Video - Dummyenabled
10Sponsored BrandsKeyword1.44339E+171.44309E+171.44297E+17enabledother1.04Keyword - Dummyexact900.00%000000.00%00Campaign Name Video - Dummyenabled
11Sponsored BrandsKeyword1.44339E+171.44309E+171.44287E+17enabledrejected1.09Keyword - Dummyexact1500.00%000000.00%00Campaign Name Video - Dummyenabled
12Sponsored BrandsKeyword1.44339E+171.44309E+171.44291E+17enabledpaused0.88Keyword - Dummyexact200.00%000000.00%00Campaign Name Video - Dummyenabled
13Sponsored BrandsKeyword1.44339E+171.44309E+171.44256E+17enabledrunning0.96Keyword - Dummyexact2900.00%000000.00%00Campaign Name Video - Dummyenabled
14Sponsored BrandsKeyword1.44339E+171.44309E+171.44222E+17enabledrunning1.04Keyword - Dummyexact14800.00%000000.00%00Campaign Name Video - Dummyenabled
15Sponsored BrandsKeyword1.44339E+171.44309E+171.44213E+17enabledrunning0.83Keyword - Dummyexact1000.00%000000.00%00Campaign Name Video - Dummyenabled
16Sponsored BrandsKeyword1.44339E+171.44309E+171.44187E+17enabledrunning0.88Keyword - Dummyexact000.00%000000.00%00Campaign Name Video - Dummyenabled
17Sponsored BrandsKeyword1.44339E+171.44309E+171.44159E+17enabledrunning1.04Keyword - Dummyexact000.00%000000.00%00Campaign Name Video - Dummyenabled
18Sponsored BrandsKeyword1.44339E+171.44309E+171.4422E+17enabledrunning1.1Keyword - Dummyexact000.00%000000.00%00Campaign Name Video - Dummyenabled
19Sponsored BrandsKeyword1.44339E+171.44309E+171.44361E+17enabledrunning0.9Keyword - Dummyexact000.00%000000.00%00Campaign Name Video - Dummyenabled
20Sponsored BrandsKeyword1.44339E+171.44309E+171.44142E+17enabledrunning0.75Keyword - Dummyexact000.00%000000.00%00Campaign Name Video - Dummyenabled
21Sponsored BrandsKeyword1.44339E+171.44309E+171.44182E+17enabledrunning0.91Keyword - Dummyexact000.00%000000.00%00Campaign Name Video - Dummyenabled
22Sponsored BrandsKeyword1.44339E+171.44309E+171.44193E+17enabledrunning0.84Keyword - Dummyexact000.00%000000.00%00Campaign Name Video - Dummyenabled
23Sponsored BrandsCampaign1.4436E+17Portfolio1Campaign Name - Dummy20210129enabledrunningdaily1000Manual####productCollectionlanding Page URL - DummyENTITYXVEHEBNLogoBrand LogoJadesASIN 12687130.48%10.200000.00%0.780Campaign Name Video - DummyproductList
Sponsored Products Campaigns
Cells with Conditional Formatting
CellConditionCell FormatStop If True
U:UOther TypeDataBarNO
V:VOther TypeDataBarNO
W:WOther TypeDataBarNO
X:XOther TypeDataBarNO
P:POther TypeDataBarNO
L:LOther TypeDataBarNO
H:HOther TypeDataBarNO
D:DOther TypeDataBarNO
D:DOther TypeDataBarNO
G1Other TypeDataBarNO
J:JOther TypeDataBarNO
K1Other TypeDataBarNO
P:POther TypeDataBarNO
T49:X1048576,T1:X1,X2:X12,W13:X25,V26:X48Other TypeDataBarNO
 
Last edited:

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
I'm guessing it's using Scientific format because the columns aren't wide enough for an actual number.
I also think the number of digits allowed in a cell is still 15 !!
 
Upvote 0
You must log in or register to reply here.

Similar threads

Q
  • Solved
Using VBA instead of Conditional formatting
2
Replies
12
Views
726
Micron
M
N
  • Question Question
My code is suddenly really slow
Replies
1
Views
369
RobP
R
S
  • Question Question
excel - conditional formatting - only for the last 50 rows of the tabl
Replies
3
Views
311
Vogateer
V
K
  • Question Question
Variable losing set Range
Replies
8
Views
449
MARK858
MARK858
S
  • Question Question
Run VBA code from specific sheet till the end of workbook
Replies
7
Views
275
Alex Blakenburg
A

Forum statistics

Threads
1,223,893
Messages
6,175,242
Members
452,623
Latest member
russelllowellpercy

Share this page

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
Back
Top