<td>Hello,<p>So I made a macro to format a table. I will post the macro below and there are comments that explain things.<\/p><p>Here’s how my table looks like: <a href=”https:\/\/web.archive.org\/web\/20201204141704\/http:\/\/i65.tinypic.com\/1606l5f.jpg” target=”_blank” rel=”nofollow”>http:\/\/i65.tinypic.com\/1606l5f.jpg<\/a><br>No, there is no shading on the cells, it’s the background I use (as a Windows OS setting).<br>I hope the picture can be displayed.<\/p><p><b>I would like to do 2 more things, and I don’t know how:<\/b><br><b>1.<\/b> Remove the white text – either remove it after the conditional formatting VBA, or replace the conditional formatting VBA with some code to remove all duplicates except first occurrence from columns A and B only.<\/p><p>I tried several codes found online and adapted, but none worked. I’m guessing because of the conditional formatting, but dunno really.<\/p><p><b>2.<\/b> Add cell color red or whatever to the entire rows that contain a client in column B, but only to those rows (they would be 2, 18 and 24 in the image). <\/p><p><b>Here is my macro without parts that I think are irrelevant to fonts and shading:<\/b><\/p><p><\/p><pre>’ Page setup: A4, Portrait, Center horizontally, Zoom 100%.
\nWith ActiveSheet.PageSetup
\n.LeftMargin = Application.CentimetersToPoints(0.196850393700787)
\n.RightMargin = Application.CentimetersToPoints(0.196850393700787)
\n.TopMargin = Application.CentimetersToPoints(0.393700787401575)
\n.BottomMargin = Application.CentimetersToPoints(0.393700787401575)
\n.HeaderMargin = Application.CentimetersToPoints(0.393700787401575)
\n.FooterMargin = Application.CentimetersToPoints(0.393700787401575)
\n.PrintHeadings = False
\n.PrintGridlines = False
\n.PrintComments = xlPrintNoComments
\n.PrintQuality = 300
\n.CenterHorizontally = True
\n.CenterVertically = False
\n.Draft = False
\n.PaperSize = xlPaperA4
\n.FirstPageNumber = xlAutomatic
\n.Order = xlDownThenOver
\n.BlackAndWhite = False
\n.Zoom = 100
\n.PrintErrors = xlPrintErrorsDisplayed
\n.OddAndEvenPagesHeaderFooter = False
\n.DifferentFirstPageHeaderFooter = False
\n.ScaleWithDocHeaderFooter = False
\n.AlignMarginsHeaderFooter = False
\nEnd With<\/p>\n
‘ Conditional formatting: color in white any duplicate values, except their first appearance.
\nRange(“A2:A279″).Select
\nSelection.FormatConditions.Add Type:=xlExpression, Formula1:=”=A1=A2”
\nSelection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
\nWith Selection.FormatConditions(1).Font
\n.ColorIndex = 2 ‘ white color
\n.TintAndShade = 0
\nEnd With
\nSelection.FormatConditions(1).StopIfTrue = False<\/p>\n
‘ Conditional formatting: color in white any duplicate values, except their first appearance.
\nRange(“B2:B279″).Select
\nSelection.FormatConditions.Add Type:=xlExpression, Formula1:=”=B1=B2”
\nSelection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
\nWith Selection.FormatConditions(1).Font
\n.ColorIndex = 2 ‘ white color
\n.TintAndShade = 0
\nEnd With
\nSelection.FormatConditions(1).StopIfTrue = False<\/p>\n
‘ Conditional formatting font face and font size for LANDSCAPE page
\n‘ arial font by default; if you want other, add line Cell.Font.Name = “Font Name”
\nFor Each Cell In ActiveSheet.UsedRange
\nIf ActiveSheet.PageSetup.Orientation = xlPortrait Then
\nCell.Font.Size = 12 ‘ font size for PORTRAIT
\nCell.Font.Name = “Tahoma”
\nElse
\nCell.Font.Size = 16 ‘ font size for LANDSCAPE
\nCell.Font.Name = “Tahoma”
\nEnd If
\nNext Cell<\/p>\n
‘ Delete text of white color in columns A and B —————————– NOT WORKING
\nDim rng As Range
\nSet rng = [B2:B279]
\nFor Each Cell In rng
\nIf Cell.Font.ColorIndex = 2 Then
\nCell.ClearContents
\nEnd If
\nNext Cell<\/p>\n
‘ Add shading to row if cell with black text is in column B —————————– TBA
\n‘ TO BE ADDED<\/pre><br><p align=”right”><font size=”1″><i>message edited by Mrrrr<\/i><\/font><\/p><\/td><\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"inline_featured_image":false,"iawp_total_views":13},"question-category":[53],"question_tags":[],"class_list":["post-6401","question","type-question","status-publish","hentry","question-category-office"],"jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/computing.net\/answers\/wp-json\/wp\/v2\/question\/6401","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/computing.net\/answers\/wp-json\/wp\/v2\/question"}],"about":[{"href":"https:\/\/computing.net\/answers\/wp-json\/wp\/v2\/types\/question"}],"author":[{"embeddable":true,"href":"https:\/\/computing.net\/answers\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/computing.net\/answers\/wp-json\/wp\/v2\/comments?post=6401"}],"wp:attachment":[{"href":"https:\/\/computing.net\/answers\/wp-json\/wp\/v2\/media?parent=6401"}],"wp:term":[{"taxonomy":"question-category","embeddable":true,"href":"https:\/\/computing.net\/answers\/wp-json\/wp\/v2\/question-category?post=6401"},{"taxonomy":"question_tags","embeddable":true,"href":"https:\/\/computing.net\/answers\/wp-json\/wp\/v2\/question_tags?post=6401"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}