An Quala
Board Regular
- Joined
- Mar 21, 2022
- Messages
- 146
- Office Version
- 2021
- Platform
- 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:
Full 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 | |||||||||||||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB | AC | AD | AE | AF | AG | AH | AI | AJ | AK | AL | AM | AN | AO | AP | AQ | AR | AS | AT | AU | AV | AW | |||
1 | Product | Entity | Operation | Campaign Id | Draft Campaign Id | Portfolio Id | Ad Group Id (Read only) | Keyword Id (Read only) | Product Targeting Id (Read only) | Campaign Name | Start Date | End Date | State | Campaign Serving Status (Informational only) | Budget Type | Budget | Bid Optimization | Bid Multiplier | Bid | Keyword Text | Match Type | Product Targeting Expression | Ad Format | Landing Page URL | Landing Page Asins | Brand Entity Id | Brand Name | Brand Logo Asset Id | Brand Logo URL | Creative Headline | Creative ASINs | Video Media Ids | Creative Type | Impressions | Clicks | Click-through Rate | Spend | Sales | Orders | Units | Conversion Rate | Acos | CPC | ROAS | Campaign Name (Informational only) | Campaign State (Informational only) | Resolved Product Targeting Expression (Informational only) | Landing Page Type (Informational only) | |||
2 | Sponsored Brands | Campaign | 1.44339E+17 | Portfolio1 | Campaign Name - Dummy | 20210130 | enabled | running | daily | 1000 | Manual | #### | productCollection | landing Page URL - Dummy | ENTITYXVEHE | BN | Logo | Brand Logo | Jades | ASIN 1 | 2092 | 7 | 0.33% | 3.87 | 26.29 | 1 | 1 | 0.14 | 14.72% | 0.55 | 6.79 | Campaign Name Video - Dummy | productList | ||||||||||||||||||
3 | Sponsored Brands | Keyword | 1.44339E+17 | 1.44309E+17 | 1.44372E+17 | enabled | running | 1.1 | Keyword - Dummy | exact | 93 | 0 | 0.00% | 0 | 0 | 0 | 0 | 0 | 0.00% | 0 | 0 | Campaign Name Video - Dummy | enabled | ||||||||||||||||||||||||||||
4 | Sponsored Brands | Keyword | 1.44339E+17 | 1.44309E+17 | 1.44378E+17 | enabled | running | 0.94 | Keyword - Dummy | exact | 1589 | 5 | 0.31% | 2.97 | 0 | 0 | 0 | 0 | 0.00% | 0.59 | 0 | Campaign Name Video - Dummy | enabled | ||||||||||||||||||||||||||||
5 | Sponsored Brands | Keyword | 1.44339E+17 | 1.44309E+17 | 1.44349E+17 | enabled | running | 1.03 | Keyword - Dummy | exact | 7 | 0 | 0.00% | 0 | 0 | 0 | 0 | 0 | 0.00% | 0 | 0 | Campaign Name Video - Dummy | enabled | ||||||||||||||||||||||||||||
6 | Sponsored Brands | Keyword | 1.44339E+17 | 1.44309E+17 | 1.44294E+17 | enabled | running | 0.94 | Keyword - Dummy | exact | 81 | 1 | 1.23% | 0.4 | 26.29 | 1 | 1 | 1 | 1.52% | 0.4 | 65.72 | Campaign Name Video - Dummy | enabled | ||||||||||||||||||||||||||||
7 | Sponsored Brands | Keyword | 1.44339E+17 | 1.44309E+17 | 1.44173E+17 | enabled | running | 1 | Keyword - Dummy | exact | 54 | 0 | 0.00% | 0 | 0 | 0 | 0 | 0 | 0.00% | 0 | 0 | Campaign Name Video - Dummy | enabled | ||||||||||||||||||||||||||||
8 | Sponsored Brands | Keyword | 1.44339E+17 | 1.44309E+17 | 1.44299E+17 | draft | running | 1 | Keyword - Dummy | exact | 54 | 1 | 1.85% | 0.5 | 0 | 0 | 0 | 0 | 0.00% | 0.5 | 0 | Campaign Name Video - Dummy | enabled | ||||||||||||||||||||||||||||
9 | Sponsored Brands | Keyword | 1.44339E+17 | 1.44309E+17 | 1.44357E+17 | enabled | landingPageNotAvailable | 0.84 | Keyword - Dummy | exact | 1 | 0 | 0.00% | 0 | 0 | 0 | 0 | 0 | 0.00% | 0 | 0 | Campaign Name Video - Dummy | enabled | ||||||||||||||||||||||||||||
10 | Sponsored Brands | Keyword | 1.44339E+17 | 1.44309E+17 | 1.44297E+17 | enabled | other | 1.04 | Keyword - Dummy | exact | 9 | 0 | 0.00% | 0 | 0 | 0 | 0 | 0 | 0.00% | 0 | 0 | Campaign Name Video - Dummy | enabled | ||||||||||||||||||||||||||||
11 | Sponsored Brands | Keyword | 1.44339E+17 | 1.44309E+17 | 1.44287E+17 | enabled | rejected | 1.09 | Keyword - Dummy | exact | 15 | 0 | 0.00% | 0 | 0 | 0 | 0 | 0 | 0.00% | 0 | 0 | Campaign Name Video - Dummy | enabled | ||||||||||||||||||||||||||||
12 | Sponsored Brands | Keyword | 1.44339E+17 | 1.44309E+17 | 1.44291E+17 | enabled | paused | 0.88 | Keyword - Dummy | exact | 2 | 0 | 0.00% | 0 | 0 | 0 | 0 | 0 | 0.00% | 0 | 0 | Campaign Name Video - Dummy | enabled | ||||||||||||||||||||||||||||
13 | Sponsored Brands | Keyword | 1.44339E+17 | 1.44309E+17 | 1.44256E+17 | enabled | running | 0.96 | Keyword - Dummy | exact | 29 | 0 | 0.00% | 0 | 0 | 0 | 0 | 0 | 0.00% | 0 | 0 | Campaign Name Video - Dummy | enabled | ||||||||||||||||||||||||||||
14 | Sponsored Brands | Keyword | 1.44339E+17 | 1.44309E+17 | 1.44222E+17 | enabled | running | 1.04 | Keyword - Dummy | exact | 148 | 0 | 0.00% | 0 | 0 | 0 | 0 | 0 | 0.00% | 0 | 0 | Campaign Name Video - Dummy | enabled | ||||||||||||||||||||||||||||
15 | Sponsored Brands | Keyword | 1.44339E+17 | 1.44309E+17 | 1.44213E+17 | enabled | running | 0.83 | Keyword - Dummy | exact | 10 | 0 | 0.00% | 0 | 0 | 0 | 0 | 0 | 0.00% | 0 | 0 | Campaign Name Video - Dummy | enabled | ||||||||||||||||||||||||||||
16 | Sponsored Brands | Keyword | 1.44339E+17 | 1.44309E+17 | 1.44187E+17 | enabled | running | 0.88 | Keyword - Dummy | exact | 0 | 0 | 0.00% | 0 | 0 | 0 | 0 | 0 | 0.00% | 0 | 0 | Campaign Name Video - Dummy | enabled | ||||||||||||||||||||||||||||
17 | Sponsored Brands | Keyword | 1.44339E+17 | 1.44309E+17 | 1.44159E+17 | enabled | running | 1.04 | Keyword - Dummy | exact | 0 | 0 | 0.00% | 0 | 0 | 0 | 0 | 0 | 0.00% | 0 | 0 | Campaign Name Video - Dummy | enabled | ||||||||||||||||||||||||||||
18 | Sponsored Brands | Keyword | 1.44339E+17 | 1.44309E+17 | 1.4422E+17 | enabled | running | 1.1 | Keyword - Dummy | exact | 0 | 0 | 0.00% | 0 | 0 | 0 | 0 | 0 | 0.00% | 0 | 0 | Campaign Name Video - Dummy | enabled | ||||||||||||||||||||||||||||
19 | Sponsored Brands | Keyword | 1.44339E+17 | 1.44309E+17 | 1.44361E+17 | enabled | running | 0.9 | Keyword - Dummy | exact | 0 | 0 | 0.00% | 0 | 0 | 0 | 0 | 0 | 0.00% | 0 | 0 | Campaign Name Video - Dummy | enabled | ||||||||||||||||||||||||||||
20 | Sponsored Brands | Keyword | 1.44339E+17 | 1.44309E+17 | 1.44142E+17 | enabled | running | 0.75 | Keyword - Dummy | exact | 0 | 0 | 0.00% | 0 | 0 | 0 | 0 | 0 | 0.00% | 0 | 0 | Campaign Name Video - Dummy | enabled | ||||||||||||||||||||||||||||
21 | Sponsored Brands | Keyword | 1.44339E+17 | 1.44309E+17 | 1.44182E+17 | enabled | running | 0.91 | Keyword - Dummy | exact | 0 | 0 | 0.00% | 0 | 0 | 0 | 0 | 0 | 0.00% | 0 | 0 | Campaign Name Video - Dummy | enabled | ||||||||||||||||||||||||||||
22 | Sponsored Brands | Keyword | 1.44339E+17 | 1.44309E+17 | 1.44193E+17 | enabled | running | 0.84 | Keyword - Dummy | exact | 0 | 0 | 0.00% | 0 | 0 | 0 | 0 | 0 | 0.00% | 0 | 0 | Campaign Name Video - Dummy | enabled | ||||||||||||||||||||||||||||
23 | Sponsored Brands | Campaign | 1.4436E+17 | Portfolio1 | Campaign Name - Dummy | 20210129 | enabled | running | daily | 1000 | Manual | #### | productCollection | landing Page URL - Dummy | ENTITYXVEHE | BN | Logo | Brand Logo | Jades | ASIN 1 | 2687 | 13 | 0.48% | 10.2 | 0 | 0 | 0 | 0 | 0.00% | 0.78 | 0 | Campaign Name Video - Dummy | productList | ||||||||||||||||||
Sponsored Products Campaigns |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
U:U | Other Type | DataBar | NO | |
V:V | Other Type | DataBar | NO | |
W:W | Other Type | DataBar | NO | |
X:X | Other Type | DataBar | NO | |
P:P | Other Type | DataBar | NO | |
L:L | Other Type | DataBar | NO | |
H:H | Other Type | DataBar | NO | |
D:D | Other Type | DataBar | NO | |
D:D | Other Type | DataBar | NO | |
G1 | Other Type | DataBar | NO | |
J:J | Other Type | DataBar | NO | |
K1 | Other Type | DataBar | NO | |
P:P | Other Type | DataBar | NO | |
T49:X1048576,T1:X1,X2:X12,W13:X25,V26:X48 | Other Type | DataBar | NO |
Last edited: