Forum Discussion

Rukmag's avatar
Rukmag
Copper Contributor
Apr 10, 2023
Solved

VBA Formating macro help

So I don't think there is something like this out there. If there is I am sorry.
I am working on a table that is exported from a different program therefore it is creating a new excel file each time.  My initial thinking would be to format the cells and apply conditional formatting to them. But since it is a new file each time that is really not viable. 

The problem is the Colours from the other program are appearing as this. Would there be a quick and simple macro solution to delete this from all cells and apply a colour depending what that text was?

<FONT color=0x000080ff>Branch 1

 

Thanks in advance 🙂

excel
Macros and VBA
  • mtarler's avatar
    mtarler
    Apr 10, 2023

    Rukmag I ran your code and didn't get any error.  That said you are in a .csv file which can't have or run macros so that may be the problem.  You can save the code in an .xlsm file and as long as the .xlsm file is open you can run the code (i.e. you don't need to copy the code into the other file).

    As for the code, it doesn't change anything because the cell value isn't = to that only the left portion.  I modified the code as follows to actually use the color that was put into that cell and keep the text:

    Sub ChangeColor()
  Dim cell As Range
  For Each cell In Selection
    If UCase(Left(cell.Value, 12)) = "<FONT COLOR=" Then
      'cell.Interior.Color = RGB(255, 165, 0)
      cell.Interior.Color = "&h" & Mid(cell.Value, 15, 8)
      cell.Value = Mid(cell.Value, 24, Len(cell.Value))
    End If
  Next cell
End Sub

    also, make sure you add a module and put the code into a module instead of the worksheet or workbook pages.  Open the Project window if not open, right click and add module:

     

3 Replies

Sort By
  • Rukmag's avatar
    Rukmag
    Copper Contributor
    Apr 10, 2023

     

    My code just results in a big ol crash haha

    • mtarler's avatar
      mtarler
      Silver Contributor
      Apr 10, 2023

      Rukmag I ran your code and didn't get any error.  That said you are in a .csv file which can't have or run macros so that may be the problem.  You can save the code in an .xlsm file and as long as the .xlsm file is open you can run the code (i.e. you don't need to copy the code into the other file).

      As for the code, it doesn't change anything because the cell value isn't = to that only the left portion.  I modified the code as follows to actually use the color that was put into that cell and keep the text:

      Sub ChangeColor()
  Dim cell As Range
  For Each cell In Selection
    If UCase(Left(cell.Value, 12)) = "<FONT COLOR=" Then
      'cell.Interior.Color = RGB(255, 165, 0)
      cell.Interior.Color = "&h" & Mid(cell.Value, 15, 8)
      cell.Value = Mid(cell.Value, 24, Len(cell.Value))
    End If
  Next cell
End Sub

      also, make sure you add a module and put the code into a module instead of the worksheet or workbook pages.  Open the Project window if not open, right click and add module:

       

      • Rukmag's avatar
        Rukmag
        Copper Contributor
        Apr 11, 2023

        mtarler 
        Hey. Thank you so much! I had no clue that CSV couldn't run any macro's by itself I would not have even considered that was an issue. Also thank you for fixing the code. I was thinking it would only affect the parts in <> since it was in the quotes.
        I added the code to a blank workbook and used that as a shortcut to put on the ribbon so it opens the .xlsm file whenever the macro is pressed hopefully avoiding any crashes.
        Thanks again for the help!

Resources