Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
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 software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old April 25th, 2018, 08:21 AM
Registered User
 
Join Date: Apr 2018
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?
 
Old April 30th, 2018, 03:16 PM
Wrox Author
 
Join Date: Apr 2011
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
 
Old April 30th, 2018, 05:06 PM
Authorized User
 
Join Date: Oct 2015
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
 
Old May 1st, 2018, 07:30 AM
Registered User
 
Join Date: Apr 2018
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.
 
Old May 1st, 2018, 11:32 AM
Wrox Author
 
Join Date: Apr 2011
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.
 
Old May 1st, 2018, 11:56 AM
Registered User
 
Join Date: Apr 2018
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.
 
Old May 1st, 2018, 12:00 PM
Wrox Author
 
Join Date: Apr 2011
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.
 
Old May 1st, 2018, 12:03 PM
Registered User
 
Join Date: Apr 2018
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

column D, starting in cell 3 (row 3)
 
Old May 1st, 2018, 12:09 PM
Wrox Author
 
Join Date: Apr 2011
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
 
Old May 1st, 2018, 12:11 PM
Registered User
 
Join Date: Apr 2018
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

thank you tomurtis!





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





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.