Mixed colur text.

[Chris Watts]

I have cells that contains a mixture of black and red text.
Other cells contain either only black or only red text.
I wish to (a) selectively locate the cells with mixed-colour text and then
(b) change the red text to bold black text. leaving any original black text
as unbold.

I have got as far as the stage of trying to detect these cells. When I use:

Cells(i, j).Select
iColour = Selection.Font.ColorIndex

Valid indexes are returned for only black or only red text contaiing cells.
But I get Invalid use of "Null" as an error message on the mixed cells. .

Can sks please suggest a solution to the two parts of this problem.

I amusing Excel 2007, under Windows XP Professional, and am using VBA.

cheers
Chris

 

[Dave Peterson]

I think you'll have to loop through all the constants text cells. (You can
eliminate the formula cells and the cells containing numbers. They can't use
this kind of character by character formatting.)

Then instead of using:
dim iColour as long
(You did declare it as a number, right?)

You could use:
dim iColour as variant 'a number or null

You'll see Null if there's a mixture of colors for the font.

If isnull(icolour) then
...

Then you'll have to loop through each character in the cell and adjust the
formatting if it meets your conditions.

This may get you to the next step:

Option Explicit
Sub testme()

Dim iColour As Variant
Dim myCell As Range
Dim myRng As Range
Dim wks As Worksheet

Set wks = Worksheets("Sheet1")

With wks
Set myRng = Nothing
On Error Resume Next
Set myRng = .Cells.SpecialCells(xlCellTypeConstants, xlTextValues)
On Error GoTo 0
End With

If myRng Is Nothing Then
MsgBox "no text constants"
Exit Sub
End If

For Each myCell In myRng.Cells
iColour = myCell.Font.ColorIndex
If IsNull(iColour) Then
MsgBox "a mixture with: " & myCell.Address
End If
Next myCell

End Sub

 

[Chris Watts]

Thanks Dave. Some excellent suggestions for me to follow there.

Luckily everything is text and yes I did declare iColour as Integer - using
variant was an angle that I had overlooked. With it I can use IsNull.

Yes chopping up the text character by character seems obvious - once an
expert has suggested it!! Many thanks.

cheers
Chris

 

[Dave Peterson]

I had to step away earlier, so I didn't have time to post this:

Option Explicit
Sub testme()
Dim iColour As Variant
Dim myCell As Range
Dim myRng As Range
Dim wks As Worksheet
Dim lCtr As Long

Set wks = Worksheets("Sheet1")

With wks
Set myRng = Nothing
On Error Resume Next
Set myRng = .Cells.SpecialCells(xlCellTypeConstants, xlTextValues)
On Error GoTo 0
End With

If myRng Is Nothing Then
MsgBox "no text constants"
Exit Sub
End If

For Each myCell In myRng.Cells
iColour = myCell.Font.ColorIndex
If IsNull(iColour) Then
For lCtr = 1 To Len(myCell.Value)
With myCell.Characters(Start:=lCtr, Length:=1).Font
If .ColorIndex = 3 Then
.ColorIndex = 1 ' 0 for none?
.Bold = True
End If
End With
Next lCtr
End If
Next myCell

End Sub

First, you can change the range to anything you want:

Set myRng = .Cells.SpecialCells(xlCellTypeConstants, xlTextValues)
to

Set myRng = .Range("A1:Z99").SpecialCells(xlCellTypeConstants, xlTextValues)
(for a specific range)

Set myRng = .Range("x:x").SpecialCells(xlCellTypeConstants, xlTextValues)
(to just look at column X)

And the color pallette can vary from workbook to workbook. For me, my default
color palette for red (at least a version of red) has a colorindex of 3.

And black is 1.

I'd record a macro when you change a couple of cells to the colors you're using
and want to use. Then you can be sure that the numbers match the colors you
want.

 

[Chris Watts]

That's just great, many thanks Dave.
Chris

I had to step away earlier, so I didn't have time to post this:

Option Explicit
Sub testme()
Dim iColour As Variant
Dim myCell As Range
Dim myRng As Range
Dim wks As Worksheet
Dim lCtr As Long

Set wks = Worksheets("Sheet1")

With wks
Set myRng = Nothing
On Error Resume Next
Set myRng = .Cells.SpecialCells(xlCellTypeConstants, xlTextValues)
On Error GoTo 0
End With

If myRng Is Nothing Then
MsgBox "no text constants"
Exit Sub
End If

For Each myCell In myRng.Cells
iColour = myCell.Font.ColorIndex
If IsNull(iColour) Then
For lCtr = 1 To Len(myCell.Value)
With myCell.Characters(Start:=lCtr, Length:=1).Font
If .ColorIndex = 3 Then
.ColorIndex = 1 ' 0 for none?
.Bold = True
End If
End With
Next lCtr
End If
Next myCell

End Sub

First, you can change the range to anything you want:

Set myRng = .Cells.SpecialCells(xlCellTypeConstants, xlTextValues)
to

Set myRng = .Range("A1:Z99").SpecialCells(xlCellTypeConstants,
xlTextValues)
(for a specific range)

Set myRng = .Range("x:x").SpecialCells(xlCellTypeConstants, xlTextValues)
(to just look at column X)

And the color pallette can vary from workbook to workbook. For me, my
default
color palette for red (at least a version of red) has a colorindex of 3.

And black is 1.

I'd record a macro when you change a couple of cells to the colors you're
using
and want to use. Then you can be sure that the numbers match the colors
you
want.