Forum Discussion
Every other row is a color that I cannot get rid of & background color will not override it
this should be very easy but I'm not seeing it!! very frustrating! trying to get rid of the blue shading in every other row. hitting the "fill" button in a white one creates the yellow highlight. doing the same in the blue one does nothing. but if you look at the cell - it tells me it's yellow. but the blue is set up somewhere else giving it some kind of "priority" or "override" capability!
Thanks for all the help!
For anyone that might stumble across this thread in the future wanting to do the same thing (replace specific characters with another font), here's what I finally came up. Tested and works well.
Sub SymbolSubstitution() Dim rng1, rng2, rng3 As Range Dim newText As String Application.ScreenUpdating = False ' Find range to search over. Set rng1 = Cells.Find("*", [a1], xlFormulas, xlPart, xlByRows, xlPrevious) Set rng2 = Cells.Find("*", [a1], xlFormulas, xlPart, xlByColumns, xlPrevious) If Not rng1 Is Nothing Then Set rng3 = Range([a1], Cells(rng1.Row, rng2.Column)) Else MsgBox "Worksheet is empty", vbExclamation, "Error" Exit Sub End If ' Loop over cells in range. For Each cell In rng3 ' Only check non-empty cells. If cell.Value <> "" Then Call ConvertToSymbolAndReplace(cell, "Δ", "D") Call ConvertToSymbolAndReplace(cell, "Φ", "F") Call ConvertToSymbolAndReplace(cell, "Ω", "W") Call ConvertToSymbolAndReplace(cell, "α", "a") Call ConvertToSymbolAndReplace(cell, "β", "b") Call ConvertToSymbolAndReplace(cell, "χ", "c") Call ConvertToSymbolAndReplace(cell, "δ", "d") Call ConvertToSymbolAndReplace(cell, "ε", "e") Call ConvertToSymbolAndReplace(cell, "η", "h") Call ConvertToSymbolAndReplace(cell, "φ", "j") Call ConvertToSymbolAndReplace(cell, "λ", "l") Call ConvertToSymbolAndReplace(cell, "μ", "m") Call ConvertToSymbolAndReplace(cell, "π", "p") Call ConvertToSymbolAndReplace(cell, "θ", "q") Call ConvertToSymbolAndReplace(cell, "×", "´") Call ConvertToSymbol(cell, "~") Call ConvertToSymbol(cell, "&") Call ConvertToSymbol(cell, "+") Call ConvertToSymbol(cell, "%") Call ConvertToSymbol(cell, "<") Call ConvertToSymbol(cell, "=") Call ConvertToSymbol(cell, ">") Call ConvertToSymbol(cell, "°") Call ConvertToSymbol(cell, "±") Call ConvertToSymbolAndReplaceUnicode(cell, &H2219, &HD7) ' Bullet (must run after × to ´) Call ConvertToSymbolAndReplaceUnicode(cell, &H2211, &H53) ' Sum/sigma sign Call ConvertToSymbolAndReplaceUnicode(cell, &H2212, &H2D) ' Minus sign Call ConvertToSymbolAndReplaceUnicode(cell, &H2264, &HA3) ' Less than or equal sign Call ConvertToSymbolAndReplaceUnicode(cell, &H2265, &HB3) ' Greater than or equal sign Call ConvertToSymbolAndReplaceUnicode(cell, &H221A, &HD6) ' Square root sign Call ConvertToSymbolAndReplaceUnicode(cell, &H221E, &HA5) ' Infinity sign Call ConvertToSymbolAndReplaceUnicode(cell, &H222B, &HF2) ' Integral sign Call ConvertToSymbolAndReplaceUnicode(cell, &H2206, &H44) ' Alternative Delta sign Call ConvertToSymbolAndReplaceUnicode(cell, &H192, &HA6) ' Function sign End If Next cell Application.ScreenUpdating = True End Sub Sub ConvertToSymbolAndReplace(ByRef thisCell As Variant, ByVal inputChar As String, ByVal outputChar As String) Dim charPos As Long charPos = InStr(thisCell.Value, inputChar) Do While charPos > 0 thisCell.Characters(charPos, 1).Text = outputChar thisCell.Characters(charPos, 1).Font.Name = "Symbol" charPos = InStr(charPos + 1, thisCell.Value, inputChar) Loop End Sub Sub ConvertToSymbol(ByRef thisCell As Variant, ByVal inputChar As String) Dim charPos As Long charPos = InStr(thisCell.Value, inputChar) Do While charPos > 0 thisCell.Characters(charPos, 1).Font.Name = "Symbol" charPos = InStr(charPos + 1, thisCell.Value, inputChar) Loop End Sub Sub ConvertToSymbolAndReplaceUnicode(ByRef thisCell As Variant, ByVal inputCharCode As Long, ByVal outputCharCode As Long) Dim charPos As Long charPos = InStr(thisCell.Value, ChrW(inputCharCode)) Do While charPos > 0 thisCell.Characters(charPos, 1).Text = ChrW(outputCharCode) thisCell.Characters(charPos, 1).Font.Name = "Symbol" charPos = InStr(charPos + 1, thisCell.Value, ChrW(inputCharCode)) Loop End Sub
2 Replies
- Riny_van_EekelenPlatinum Contributor
Ed_Novy Select the entire sheet. On the Home ribbon, select Conditional Formatting and Clear Rules (from entire sheet). That should do it.
- Ed_NovyCopper Contributor
Riny_van_EekelenThank You VERY MUCH!!!! I tried that & it worked perfect! I never saw this before, the boss asked me to try, I did for an hour then gave up & tried this group. I tried reading the rule language to understand how to do this - not happening any time soon! Maybe one day but the accounting business has been nuts with SBA loans, etc this past year & it still is. Your email went to my "Junk" folder, I just found it an hour ago. Thanks again!!