Mixed (colour) formatting text and numbers in one cell

[MartinS13X]

I am creating a large table which will have repeating column names or at least, that's what I want it to look like.
The column name will consist of a text component and a numerical component.

The text component will have "regular formatting", but the numbers will be formatted the same colour as the cell they are in so they blend in with the background and it will appear as if there is only text in the cell.

Doing it manually is a bit of a chore (365 * 8 (or more columns)) and I would like to know if there is a way to do this automagically, either through conditional formatting or with VBA.

Thanks for any advice and/or help

 

[KOKOSEK]

Set conditional formatting with formula:

=MOD(INT((COLUMN()+2)/8),2)=1

for 1st colour and =0 for 2nd colour on whole interesting range.

 

[Peter_SSs]

Give this a try after adjusting the range in the code.

Sub Hide_Numbers()
  Dim RX As Object, M As Object
  Dim c As Range
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.Pattern = "\d+"
  For Each c In Range("D4:S4")  '<- Adjust range to suit
    If RX.Test(c.Value) Then
      Set M = RX.Execute(c.Value)(0)
      c.Characters(M.Firstindex + 1, Len(M)).Font.Color = c.DisplayFormat.Interior.Color
    End If
  Next c
End Sub

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[MartinS13X]

Give this a try after adjusting the range in the code.

Sub Hide_Numbers()
  Dim RX As Object, M As Object
  Dim c As Range
 
  Set RX = CreateObject("VBScript.RegExp")
  RX.Pattern = "\d+"
  For Each c In Range("D4:S4")  '<- Adjust range to suit
    If RX.Test(c.Value) Then
      Set M = RX.Execute(c.Value)(0)
      c.Characters(M.Firstindex + 1, Len(M)).Font.Color = c.DisplayFormat.Interior.Color
    End If
  Next c
End Sub

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

This worked a treat... thank you so much.
Saved me a LOT of time.

I'll also update my account. thanks.

 

[MartinS13X]

Set conditional formatting with formula:

=MOD(INT((COLUMN()+2)/8),2)=1

for 1st colour and =0 for 2nd colour on whole interesting range.

I've played around with this a bit, but couldn't get it to work. or at least not how I wanted it.
I'm interested to know how it works though.

=MOD(INT((COLUMN()+1)/1),1)=1

and other variants seemed to have the same effect as your values?
I'm assuming the 8 is the number of columns I'm using, but that's about the extent of my understanding of what this does.

 

[nemmi69]

Could use 3 conditions for ISTEXT, ISNUMBER & ISBLANK. Remember to highlight entire range to be conditioned and make sure cell ref contains no $'s

 

[Peter_SSs]

I've played around with this a bit, but couldn't get it to work. or at least not how I wanted it.

The conditional formatting will not allow you to format only part of the cell text with a particular font colour like the code I provided does.