Mixed (colour) formatting text and numbers in one cell

MartinS13X

New Member
Joined
Apr 4, 2018
Messages
17
Office Version
  1. 2019
Platform
  1. Windows
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 :)

ExcelFormat.jpg
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Set conditional formatting with formula:

Rich (BB code):
=MOD(INT((COLUMN()+2)/8),2)=1

for 1st colour and =0 for 2nd colour on whole interesting range.
 
Upvote 0
Give this a try after adjusting the range in the code.

VBA 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’)
 
Upvote 1
Solution
Give this a try after adjusting the range in the code.

VBA 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.
 
Upvote 0
Set conditional formatting with formula:

Rich (BB code):
=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.
 
Upvote 0
Could use 3 conditions for ISTEXT, ISNUMBER & ISBLANK. Remember to highlight entire range to be conditioned and make sure cell ref contains no $'s


1683265858806.png

1683265929645.png

1683266049979.png
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,569
Latest member
Honeymonster123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top