conditional formatting on specific text in cells

[kwkhoo]

hi i have a problem

i want to do the following:

a b c d e f g h
1 3 3 3 3-3-3 3 4 5 3-4-5

d1 has formula =a1&"-"&a2&"-"&a3 to combine cells a1 to c1 to get 3-3-3
d2 has formula =e1&"-"&f2&"-"&g3 to combine cells e1 to g1 to get 3-4-5

how can i make the digit "4" and "5" be colored red to reflect the
changes from 3-3-3 to 3-4-5?

secondly, using the table above (perhaps related)

if i apply conditional formatting to cell g1, and it turns red
and i apply the formula "=g1" to a new cell I2 , it only shows the
value, but not the red color.
how do it get it to show red color in I2

i'm close to pulling my hair out. banging on the wall don't help.

thanks!

 

[Bernie Deitrick]

1) Conditional formatting cannot change the color of parts of strings. You
could use a worksheet calculate event, but that would also require the use
of two 'helper' cells.

2) Conditional formatting only applies to the cell which it is added to. If
you want I2 to change the same way as G2, then you need to use the same
conditional formatting on cell I2 as on G2.

i'm close to pulling my hair out. banging on the wall don't help.

It only helps if you bang the bald spot on your head (from pullin your hair
out) on the wall.

HTH,
Bernie
MS Excel MVP

 

[kwkhoo]

understood. thanks bernie

1) what if i convert the formulas into values? will it help?

i have converted the H1 cell to a value of "3-4-5" and D1 cell to
"3-3-3", i've got bits and pieces of codes here, (thanks ya all) but i
don't know how to compile them into one nice macro due to my lack of vb
skills

it's goes along like,
if e1>a1, then Left(H1,1) = red, else do nothing
if f1>b1, then mid(H1,3,1) = red, else do nothing
if g1>c1, then right(H1,1) = red, else do nothing

this is what i found to color it red and bold:

Function SECTORA()

With ActiveCell.Characters(Start:=1, Length:=1).Font
.ColorIndex = 3
.Bold = True

End With

End Function

2) so you mean even if i color my text first using conditional format
in G2, there's no way to "copy the colors" without doing a conditional
format.

 

[Bernie Deitrick]

1) Below is code that will accomplish #1

2) Yes. Though you could copy cell G2, then pastespecial formats onto other
cells.

HTH,
Bernie
MS Excel MVP


Sub TryNow()
Dim i As Integer

Range("D1").Value = Range("D1").Value
Range("H1").Value = Range("H1").Value
For i = 1 To Len(Range("D1").Value)
If Mid(Range("D1").Value, i, 1) <> Mid(Range("H1").Value, i, 1) Then
With Range("D1").Characters(Start:=i, Length:=1).Font
.ColorIndex = 3
.Bold = True
End With
With Range("H1").Characters(Start:=i, Length:=1).Font
.ColorIndex = 3
.Bold = True
End With
End If
Next i

End Sub