Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old April 25th, 2018, 08:21 AM
Registered User
Points: 19, Level: 1
Points: 19, Level: 1 Points: 19, Level: 1 Points: 19, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Apr 2018
Location: Martinsburg, WV
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default Need to change font on specific text ONLY

I have many cells that have data like:
[..data..data..data..data]

I need to change the font color in JUST the bracketed part and not anything else in the cell. Can anybody help here?
Reply With Quote
  #2 (permalink)  
Old April 30th, 2018, 03:16 PM
Wrox Author
Points: 60, Level: 1
Points: 60, Level: 1 Points: 60, Level: 1 Points: 60, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Apr 2011
Location: San Francisco / Silicon Valley
Posts: 16
Thanks: 0
Thanked 2 Times in 2 Posts
Default

Is this data static as a constant value, meaning it is NOT produced by a formula?

If the data is produced by a formula, you cannot accomplish the task you speak of.

If the data is not produced by a formula, meaning it is a constant value, you can do this to (for example) the active cell with this macro to make the text inside and including the square brackets be of a contrasting color, red in this example.

Code:
Sub ColorFont()
With ActiveCell
Dim StartChar&, EndChar&, strFull$
strFull = .Value
If InStr(1, strFull, "[", 1) > 0 And InStr(1, strFull, "]", 1) > 0 Then
.Font.Color = vbBlack
StartChar = Application.Search("[", strFull)
EndChar = Application.Search("]", strFull)
.Characters(Start:=StartChar, Length:=EndChar - StartChar + 1).Font.Color = vbRed
End If
End With
End Sub
Reply With Quote
  #3 (permalink)  
Old April 30th, 2018, 05:06 PM
Authorized User
Points: 192, Level: 3
Points: 192, Level: 3 Points: 192, Level: 3 Points: 192, Level: 3
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2015
Location: South Africa
Posts: 48
Thanks: 0
Thanked 5 Times in 5 Posts
Default Work around

Tomurtis is correct.
However, you could use a script that copies and pastes values and number formatting in place.
Then call His function to do the formatting.
In turn, tie this function to an event.

Possible candidates are the onCalculate, onChange and the onCalculate events.
__________________
Nostalgia 4 Infinity
Reply With Quote
  #4 (permalink)  
Old May 1st, 2018, 07:30 AM
Registered User
Points: 19, Level: 1
Points: 19, Level: 1 Points: 19, Level: 1 Points: 19, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Apr 2018
Location: Martinsburg, WV
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks TOMURTUS - I think you have what I need. BTW, it is static characters, not subject to change or generated by a formula.
Reply With Quote
  #5 (permalink)  
Old May 1st, 2018, 11:32 AM
Wrox Author
Points: 60, Level: 1
Points: 60, Level: 1 Points: 60, Level: 1 Points: 60, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Apr 2011
Location: San Francisco / Silicon Valley
Posts: 16
Thanks: 0
Thanked 2 Times in 2 Posts
Default

You're quite welcome. Post back if you have many cells in a range with varying string lengths and you need a loop for this instead of just for one cell.
Reply With Quote
  #6 (permalink)  
Old May 1st, 2018, 11:56 AM
Registered User
Points: 19, Level: 1
Points: 19, Level: 1 Points: 19, Level: 1 Points: 19, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Apr 2018
Location: Martinsburg, WV
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Yeah I have one column (cell) in a long range of rows. The range may vary in length.
Reply With Quote
  #7 (permalink)  
Old May 1st, 2018, 12:00 PM
Wrox Author
Points: 60, Level: 1
Points: 60, Level: 1 Points: 60, Level: 1 Points: 60, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Apr 2011
Location: San Francisco / Silicon Valley
Posts: 16
Thanks: 0
Thanked 2 Times in 2 Posts
Default

Which column is it, and in what cell does the list begin. I realize the list can be any length, I don't need to know the last cell of the list, just the column and what cell you prefer to start in.
Reply With Quote
  #8 (permalink)  
Old May 1st, 2018, 12:03 PM
Registered User
Points: 19, Level: 1
Points: 19, Level: 1 Points: 19, Level: 1 Points: 19, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Apr 2018
Location: Martinsburg, WV
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

column D, starting in cell 3 (row 3)
Reply With Quote
  #9 (permalink)  
Old May 1st, 2018, 12:09 PM
Wrox Author
Points: 60, Level: 1
Points: 60, Level: 1 Points: 60, Level: 1 Points: 60, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Apr 2011
Location: San Francisco / Silicon Valley
Posts: 16
Thanks: 0
Thanked 2 Times in 2 Posts
Default

This will do that:

Code:
Sub ColorFontManyCells()
Application.ScreenUpdating = False
Dim cell As Range, xRow As Long, LastRow As Long
LastRow = Cells(Rows.Count, 4).End(xlUp).Row
Range("D3:D" & LastRow).Font.Color = vbBlack
For Each cell In Range("D3:D" & LastRow).SpecialCells(2)
Dim StartChar&, EndChar&, strFull$
strFull = cell.Value
If InStr(1, strFull, "[", 1) > 0 And InStr(1, strFull, "]", 1) > 0 Then
StartChar = Application.Search("[", strFull)
EndChar = Application.Search("]", strFull)
cell.Characters(Start:=StartChar, Length:=EndChar - StartChar + 1).Font.Color = vbRed
End If
Next cell
Application.ScreenUpdating = True
End Sub
Reply With Quote
  #10 (permalink)  
Old May 1st, 2018, 12:11 PM
Registered User
Points: 19, Level: 1
Points: 19, Level: 1 Points: 19, Level: 1 Points: 19, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Apr 2018
Location: Martinsburg, WV
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

thank you tomurtis!
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
MessageBox Font change vgsgowrisankar C# 2005 5 April 8th, 2008 04:10 AM
How to change font using winspool FileFound Visual Studio 2005 0 July 9th, 2007 04:57 AM
Thai Font Change in ??????? Montri SQL Server ASP 1 June 12th, 2006 10:35 PM
Write to a text file in a specific font and color shai505 C# 1 May 21st, 2006 03:03 AM
how to get font ,color,font size of text in win32 satishsatao Visual C++ 0 April 5th, 2006 05:05 AM



All times are GMT -4. The time now is 07:47 AM.


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.