VBA to change colour of a particular character

[RyanChristy]

Dear all,

I have two cells each with a data validation list.
The list from the second box is dependant upon the selection of the first.
I have done this by using CTRL SHIFT F3 on the table of data required for the second data validation list, making indirect name ranges for the options in the first data validation list.
As you cannot put spaces in named ranges - excel has added an '_' to replace the spaces. The only way this now works is if I change the inputs to the first data validation list by removing the spaces and replacing them with '_'.
This works fine - but I don't want '_' visible on the form.

I have searched many forums for ways for VBA to change font colour for a particular character - however this is dependant on either:
The cell already having differences in the font already or;
The location of the character within the cell being determined. i.e the fith character for length of 1.

Can VBA identify the '_' in the cell and change it from automatic to white so it is no longer visible?

Many Thanks in Advance.

Ryan

 

[Case_Germany]

Hi,

the following code applies to Sheet1 cell D3:

Option Explicit
Sub Main()
    With Sheet1.Cells(3, 4)
        .Characters(Start:=InStr(.Value, "_"), Length:=1).Font.Color = xlNone
    End With
End Sub

This can be automated via the event "Worksheet_Change".

 

[RyanChristy]

Thank you very much for your help.

I'm having a little bit of trouble adapting it to my spreadsheet and range.

The spreadsheet is titled 'Live Report' and the range of cells this would be required for is B167:I205.

That range is larger than it needs to be, as there are multiple smaller ranges within that which actually need the macro (I just gave a larger range for simplicity). Is it more efficient to have the one large range or multiple smaller ranges?

Thank you for your patience.

Ryan

 

[Peter_SSs]

Ryan

Welcome to the MrExcel board!

I have allowed for
- the range you have given, and
- the case where there may be more than one underscore in a cell (eg "Stock_Part_Numbers"), and
- the case where any of the cells may have been shaded. So instead of just making the underscore white, this code makes it the same as the colour of the cell

See how it goes on your sheet.

<font face=Courier New><br><br><SPAN style="color:#00007F">Sub</SPAN> FixUnderscores()<br>    <SPAN style="color:#00007F">Dim</SPAN> USFound <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> FirstAddress <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> Pos <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#00007F">With</SPAN> Sheets("Live Report").Range("B167:I205")<br>        <SPAN style="color:#00007F">Set</SPAN> USFound = .Find(What:="_", LookIn:=xlValues, _<br>            LookAt:=xlPart, SearchFormat:=False)<br>        <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> USFound <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>            FirstAddress = USFound.Address<br>            <SPAN style="color:#00007F">Do</SPAN><br>                <SPAN style="color:#00007F">With</SPAN> USFound<br>                    Pos = InStr(1, .Value, "_")<br>                    <SPAN style="color:#00007F">Do</SPAN><br>                        .Characters(Start:=Pos, Length:=1) _<br>                            .Font.Color = .Interior.Color<br>                        Pos = InStr(Pos + 1, .Value, "_")<br>                    <SPAN style="color:#00007F">Loop</SPAN> <SPAN style="color:#00007F">While</SPAN> Pos > 0<br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>                <SPAN style="color:#00007F">Set</SPAN> USFound = .FindNext(After:=USFound)<br>            <SPAN style="color:#00007F">Loop</SPAN> <SPAN style="color:#00007F">While</SPAN> USFound.Address <> FirstAddress<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[RyanChristy]

Peter,

Thank you very much, that has worked perfectly. And thanks for the case of two '_', that is something I forgot to mention.

Where would I add the code to make it so it ran whenever there was a change? I'm having to run the macro manually at the moment.

Many Thanks

Ryan

 

[Peter_SSs]

Peter,

Thank you very much, that has worked perfectly. And thanks for the case of two '_', that is something I forgot to mention.

Where would I add the code to make it so it ran whenever there was a change? I'm having to run the macro manually at the moment.

Many Thanks

Ryan

Leave the existing code in a standard module and ..

1. Right click the 'Live Report' sheet name tab and choose "View Code".

2. Copy and Paste the code below into the main right hand pane that opens at step 1.

3. Close the Visual Basic window.

4. Try making changes.

This should cause the code to run whenever a change is made in the B167:I207 range. That means the code will run more often than is needed. It would be more efficient if we knew the smaller ranges within that range where this was needed. Even better would be if these could be named ranges so that the code would not fail if subsequently rows/columns are added/deleted.


<font face=Courier New><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">Const</SPAN> CheckRanges <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "B167:I207"<br>    <br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Intersect(Target, Range(CheckRanges)) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        FixUnderscores<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>

 

[RyanChristy]

Thank you very much Peter
It works perfectly.

Ryan