VB code not working properly

[asad]

Hello Guys,

I got the following sheet with a worksheet change event code. The code is supposed to show $174.34 in bold and red and the rest of text in A2 as normal black fonts. But is showing a portion of the other text in bold and red. Can you please tell me what is wrong with my code?

Excel Workbook
	A	B	C	D	E
2	In this cost, Driver costs, Running & Maintenance, and Overheads costs are Avoidable Costs. These come to $ 174.34.
3	In this cost, Driver costs, Running & Maintenance, and Overheads costs are Avoidable Costs. These come to $ 174.34.
4	$ 174.34
5
6
7
8
9	Working Notes and explanations for getting the figures
10
11		Unit Measures	# of Units	Unit Cost	Total Cost
12
13	Driver Costs	Casual Driver min Hrs	3:00	$ 49.78	$ 149.34
14	Running and Maintenance	Contract Kilometres	30	$ 0.62	$ 18.66
15	Overheads	Buses x Hours	1:20	$ 4.75	$ 6.33
16	Capital Cost	Buses x Hours	1:20	$ 23.96	$ 31.95
17
18				Total	$ 206.28
Sheet

Excel Workbook
	E
13	$ 149.34
14	$ 18.66
15	$ 6.33
6Hi

Private Sub Worksheet_Change(ByVal Target As Range)
Dim objISect As Range
Set objISect = Intersect(Target, Range("C13:D16"))
If Not objISect Is Nothing Then
 
    Range("A2").Value = Range("A3").Text
 
    Range("A2").Font.Bold = False
    Range("A2").Font.ColorIndex = 0
 
    Range("A2").Characters(InStr(1, Range("A2").Text, Range("A4").Text), _
                        Len(Range("A4").Text)).Font.Bold = True
    Range("A2").Characters(InStr(1, Range("A2").Text, Range("A4").Text), _
                        Len(Range("A4").Text)).Font.ColorIndex = 3
 
End If
End Sub

 

[asad]

Could it be because of format of numbers in cells?

 

[meldoc]

Hello

Try.

<font face=Courier New>  Range("A2").Characters(InStr(1, Range("A2").Text, <SPAN style="color:#00007F">CDec</SPAN>(Range("A4").Value)), _<br>        Len(Range("A4").Text)).Font.Bold = <SPAN style="color:#00007F">True</SPAN><br>  Range("A2").Characters(InStr(1, Range("A2").Text, <SPAN style="color:#00007F">CDec</SPAN>(Range("A4").Value)), _<br>        Len(Range("A4").Text)).Font.ColorIndex = 3<br></FONT>

 

[asad]

Still the same result. Only this part

In this cost, Driver costs, Running &

is getting red and bold not the one I want.

 

[JoeMo]

If cell A4 is a number formatted as currency, Excel ignores the $ sign. So you need to use Range("A4").Value instead of Range("A4").Text like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim objISect As Range
Set objISect = Intersect(Target, Range("C13:D16"))
If Not objISect Is Nothing Then
 
    Range("A2").Value = Range("A3").Text
 
    Range("A2").Font.Bold = False
    Range("A2").Font.ColorIndex = 0
 
    Range("A2").Characters(InStr(1, Range("A2").Text, Range("A4").Value), _
                        Len(Range("A4").Value)).Font.Bold = True
    Range("A2").Characters(InStr(1, Range("A2").Text, Range("A4").Value), _
                        Len(Range("A4").Value).Font.ColorIndex = 3
 
End If
End Sub

 

[asad]

Thanks JoeMo, but now it is highlighting

[B][COLOR=red]In this[/COLOR][/B]

. Still not highlighting the amount.

 

[gregtx81]

Hi asad,

It would be helpful to know the formula of A3.

That said, the problem the is .Text property doesn't function like one might expect. For instance, if I type "174.34" in A1, apply a currency format, Range("A1").Text will appear to be "$ 174.34", however, len(Range("A1").Text)=13 rather than 8. This apparent discrepancy is causing issues with the Instr function you are using.

You might try

    Range("A2").Characters(InStr(1, Range("A2").Text, "$ " & Range("A4").Value), _
                        Len("$ " & Range("A4").Value)).Font.Bold = True

    Range("A2").Characters(InStr(1, Range("A2").Text, "$ " & Range("A4").Value), _
                        Len("$ " & Range("A4").Value)).Font.ColorIndex = 3

as opposed to using Range("A4").Text

This is essentially the same as JoeMo's suggestion, but highlights the "$" as well.

 

[asad]

Hello gregtx81,

It still is not working. In A3, I am using:

="In this cost, Driver costs, Running & Maintenance, and Overheads costs are Avoidable Costs. These come to $ "&TEXT($A$4,"00.00")&"."

Thanks for your time and patience guys. I really appreciate your help.

Asad

 

[asad]

.

 

[meldoc]

Hello again

Try.

<font face=Courier New><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">Dim</SPAN> objISect <SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#00007F">Dim</SPAN> a <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, b <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><SPAN style="color:#00007F">Set</SPAN> objISect = Intersect(Target, Range("C13:D16"))<br><SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> objISect <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br><br>    Range("A2").Value = Range("A3").Text<br> <br>    Range("A2").Font.Bold = <SPAN style="color:#00007F">False</SPAN><br>    Range("A2").Font.ColorIndex = 0<br>    <br>    a = (Range("A4").Value)<br>    b = Format(a, "00.00")<br>    <br> <br>    Range("A2").Characters(InStr(1, Range("A2").Text, b) - 2, _<br>      Len(b) + 2).Font.Bold = <SPAN style="color:#00007F">True</SPAN><br>    Range("A2").Characters(InStr(1, Range("A2").Text, b) - 2, _<br>      Len(b) + 2).Font.ColorIndex = 3<br>                        <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></FONT>