excel rows 1

[100dtl]

Hi

Not sure if i'm in the right forum/...

Is this possible in excel VB module?, if i type in a text value, eg: final

the row then looks at this value and colors the row in a specified colour? I would like different colors for different text values ie:

final1 = red
final2 = green
final3 = yellow
final4 = blue

etc.

MANY THXS in advance

 

[MakeItSo]

Try this:

Cells.Find(What:=YourSearchtext, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
Select Case YourSearchtext
Case "final"
Selection.Font.ColorIndex = 3 'Red
Case "myGreen"
Selection.Font.ColorIndex = 4 'Green
Case "myYellow"
Selection.Font.ColorIndex = 6 'Yellow
Case "myBlue"
Selection.Font.ColorIndex = 5 'Blue
End Select

OK?
MakeItSo

Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[100dtl]

Where does this go?

 

[100dtl]

Another is if my YourSearchtext was one column, how would i do that?

 

[MakeItSo]

Oh, I thought you already had a macro... ;-)
BTW: The above code is partially non-sense... :-/

Well then:
Go to tools - macros and create a macro, e.g. "ColorIt"
In VB-Editor paste this:

Sub ColorIt()
Dim starter
Dim i As Integer
Dim myCrits As Variant
Dim myColors As Variant
Dim crit As String

myCrits = Array("final", "green", "yellow", "blue&quot
myColors = Array("3", "4", "6", "5&quot

For i = 0 To UBound(myCrits)
crit = myCrits(i)
Cells.Find(What:=crit, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
Do While True
Selection.Font.ColorIndex = myColors(i)
Set starter = ActiveCell
Cells.FindNext.Activate
If ActiveCell.Address = starter.Address Then Exit Do
Loop
Next i
End Sub

I've tested it and it seems to work fine...
Cheers,
MakeItSo

Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[100dtl]

Cool, well neally, made up with hat you have done but... I want the row to change color not the text :-\ is this possible?

 

[100dtl]

I'd like to have it always running aswell.. peeeez

 

[xlbo]

trust me - you don't want it always running - how are you gonna work on the sheet if the code is always running and making changes - run it from the worksheet change event and check to see what column has been changed....

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[100dtl]

Ok,... no problem, I've put a button on... Anyone help with the colouring of the row?? instead of the text inthe cell?

 

[shaddow]

For the row you should use this

   Selection.EntireRow.Font.ColorIndex = myColors(i)
OR
   Selection.EntireColumn.Font.ColorIndex = myColors(i)

________
George, M

 

[MakeItSo]

OK, here it comes: (I had to change a few lines, so just overwrite your old code with it)

Sub ColorIt()
Dim starter
Dim i As Integer
Dim myCrits As Variant
Dim myColors As Variant
Dim crit As String

myCrits = Array("final", "green", "yellow", "blue&quot
myColors = Array("3", "4", "6", "5&quot

For i = 0 To UBound(myCrits)
crit = myCrits(i)
Cells.Find(What:=crit, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
Do While True
Set starter = ActiveCell
Rows(ActiveCell.Row).Select
Selection.Font.ColorIndex = myColors(i)
starter.Select
Cells.FindNext.Activate
If ActiveCell.Address = starter.Address Then Exit Do
Loop
Next i
End Sub



MakeItSO

Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[100dtl]

It still did text but thats fine with me, !!! nice one

 

[MakeItSo]

Thanks a lot!
Anytime again..

Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[xlbo]

to change the background colour, instead of:

Rows(ActiveCell.Row).Select
Selection.Font.ColorIndex = myColors(i)

use

Rows(ActiveCell.Row).interior.ColorIndex = myColors(i)

in fact, even if you don't want to change from font to background, use this syntax. Select statements are very rarely necessary and they just slow the code down


Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244