hello all,

I was wondering if anyone could help me out with this. I want to be able to
change the background and font color of any cell where text is equal to a
specific text.
I do not know if I have the right syntax, but here goes

If text = "Fail" then
Range("A1:J19").Select
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
Selection.Font.ColorIndex = 2
Selection.Font.Bold = True
Else
If text = "Pass" Then
Range("A1:J19").Select
Selection.Interior.ColorIndex = 5
Selection.Font.ColorIndex = 2
Selection.Font.Bold = False
Else
If Text = "WIP" Then
Range("A1:J19").Select
With Selection.Interior
.ColorIndex = 10
.Pattern = xlSolid
End With
Selection.Font.ColorIndex = 2
Selection.Font.Bold = True
End If
End If
End If

Any and all help in the matte is greatly appreciated

On Feb 8, 3:02*pm, "Argus Rogue" <argusro...@satx.rr.com> wrote:
> hello all,
>
> I was wondering if anyone could help me out with this. *I want to be able to
> change the background and font color of any cell where text is equal to a
> specific text.
> I do not know if I have the right syntax, but here goes
>
> If text = "Fail" then
> * Range("A1:J19").Select
> * * With Selection.Interior
> * * * * .ColorIndex = 3
> * * * * .Pattern = xlSolid
> * * End With
> * * Selection.Font.ColorIndex = 2
> * * Selection.Font.Bold = True
> Else
> * * If text = "Pass" Then
> * * * * Range("A1:J19").Select
> * * * * Selection.Interior.ColorIndex = 5
> * * * * Selection.Font.ColorIndex = 2
> * * * * Selection.Font.Bold = False
> * * Else
> * * If Text = "WIP" Then
> * * * * Range("A1:J19").Select
> * * * * With Selection.Interior
> * * * * .ColorIndex = 10
> * * * * .Pattern = xlSolid
> * * * * End With
> * * * * Selection.Font.ColorIndex = 2
> * * * * Selection.Font.Bold = True
> * * End If
> End If
> End If
>
> Any and all help in the matte is greatly appreciated

How is [text] referenced? Do you get it from a Cell, or is it in your
VBA-Process?

If you only need to distinguish between 3 statements (Fail, Pass and
WIP) then i would recommend "Conditional Formatting"
You can select Range A1:J19, then goto Format --> Conditional
Formatting, there you choose "Formula is" and then you enter
=$A$20="Fail"
then you select the Pattern and everything, then you press Add... and
do the same again for Pass and WIP.
You only have 3 possibilities...if you need more then we have to go
back to your VBA code.
(Look up "Select Case" and don't use selection, but we can check that
later)

Hth
Carlo

No I also have the following text
Pending
Broken
Running
Not Completed

Each Week I have to update this spreadsheet

A B C D E
1 ENV 02/01/08 02/08/08 02/15/08
2 Web Pass Pass Broken
3 MFrame Pass Fail Fixed
4 GUI WIP Pending Running
5
after our health check we go into this spreadsheet and update it. I was
using conditional formatting but I only could add three conditional. also
since they shared the workbook to everyone, conditional formatting is no
longer working....

That is why i was trying to create a macro that we could run that would
change the cell background and font based on the text in the cell

Thanks for the help

Argus

"carlo" <(E-Mail Removed)> wrote in message
news:e68143a6-5cf5-4fb8-9714-(E-Mail Removed)...
On Feb 8, 3:02 pm, "Argus Rogue" <argusro...@satx.rr.com> wrote:
> hello all,
>
> I was wondering if anyone could help me out with this. I want to be able
> to
> change the background and font color of any cell where text is equal to a
> specific text.
> I do not know if I have the right syntax, but here goes
>
> If text = "Fail" then
> Range("A1:J19").Select
> With Selection.Interior
> .ColorIndex = 3
> .Pattern = xlSolid
> End With
> Selection.Font.ColorIndex = 2
> Selection.Font.Bold = True
> Else
> If text = "Pass" Then
> Range("A1:J19").Select
> Selection.Interior.ColorIndex = 5
> Selection.Font.ColorIndex = 2
> Selection.Font.Bold = False
> Else
> If Text = "WIP" Then
> Range("A1:J19").Select
> With Selection.Interior
> .ColorIndex = 10
> .Pattern = xlSolid
> End With
> Selection.Font.ColorIndex = 2
> Selection.Font.Bold = True
> End If
> End If
> End If
>
> Any and all help in the matte is greatly appreciated

How is [text] referenced? Do you get it from a Cell, or is it in your
VBA-Process?

If you only need to distinguish between 3 statements (Fail, Pass and
WIP) then i would recommend "Conditional Formatting"
You can select Range A1:J19, then goto Format --> Conditional
Formatting, there you choose "Formula is" and then you enter
=$A$20="Fail"
then you select the Pattern and everything, then you press Add... and
do the same again for Pass and WIP.
You only have 3 possibilities...if you need more then we have to go
back to your VBA code.
(Look up "Select Case" and don't use selection, but we can check that
later)

Hth
Carlo

try
Sub colorcells()
mc = "e"
lr = Cells(Rows.Count, mc).End(xlUp).Row
For Each c In Range(Cells(2, mc), Cells(lr, mc))
Select Case UCase(c)
Case "PENDING": myc = 5
Case "BROKEN": myc = 36
Case "RUNNING": myc = 8
'Not Completed
'as many as you need
Case Else
End Select
c.Interior.ColorIndex = myc
Next
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Argus Rogue" <(E-Mail Removed)> wrote in message
news:47ac36f0$0$30677$(E-Mail Removed)...
> No I also have the following text
> Pending
> Broken
> Running
> Not Completed
>
> Each Week I have to update this spreadsheet
>
> A B C D E
> 1 ENV 02/01/08 02/08/08 02/15/08
> 2 Web Pass Pass Broken
> 3 MFrame Pass Fail Fixed
> 4 GUI WIP Pending Running
> 5
> after our health check we go into this spreadsheet and update it. I was
> using conditional formatting but I only could add three conditional. also
> since they shared the workbook to everyone, conditional formatting is no
> longer working....
>
> That is why i was trying to create a macro that we could run that would
> change the cell background and font based on the text in the cell
>
> Thanks for the help
>
> Argus
>
> "carlo" <(E-Mail Removed)> wrote in message
> news:e68143a6-5cf5-4fb8-9714-(E-Mail Removed)...
> On Feb 8, 3:02 pm, "Argus Rogue" <argusro...@satx.rr.com> wrote:
>> hello all,
>>
>> I was wondering if anyone could help me out with this. I want to be able
>> to
>> change the background and font color of any cell where text is equal to a
>> specific text.
>> I do not know if I have the right syntax, but here goes
>>
>> If text = "Fail" then
>> Range("A1:J19").Select
>> With Selection.Interior
>> .ColorIndex = 3
>> .Pattern = xlSolid
>> End With
>> Selection.Font.ColorIndex = 2
>> Selection.Font.Bold = True
>> Else
>> If text = "Pass" Then
>> Range("A1:J19").Select
>> Selection.Interior.ColorIndex = 5
>> Selection.Font.ColorIndex = 2
>> Selection.Font.Bold = False
>> Else
>> If Text = "WIP" Then
>> Range("A1:J19").Select
>> With Selection.Interior
>> .ColorIndex = 10
>> .Pattern = xlSolid
>> End With
>> Selection.Font.ColorIndex = 2
>> Selection.Font.Bold = True
>> End If
>> End If
>> End If
>>
>> Any and all help in the matte is greatly appreciated
>
> How is [text] referenced? Do you get it from a Cell, or is it in your
> VBA-Process?
>
> If you only need to distinguish between 3 statements (Fail, Pass and
> WIP) then i would recommend "Conditional Formatting"
> You can select Range A1:J19, then goto Format --> Conditional
> Formatting, there you choose "Formula is" and then you enter
> =$A$20="Fail"
> then you select the Pattern and everything, then you press Add... and
> do the same again for Pass and WIP.
> You only have 3 possibilities...if you need more then we have to go
> back to your VBA code.
> (Look up "Select Case" and don't use selection, but we can check that
> later)
>
> Hth
> Carlo
>

On Feb 8, 11:34*am, "Don Guillett" <dguille...@austin.rr.com> wrote:
> try
> Sub colorcells()
> mc = "e"
> lr = Cells(Rows.Count, mc).End(xlUp).Row
> For Each c In Range(Cells(2, mc), Cells(lr, mc))
> Select Case UCase(c)
> Case "PENDING": myc = 5
> Case "BROKEN": myc = 36
> Case "RUNNING": myc = 8
> 'Not Completed
> 'as many as you need
> Case Else
> End Select
> c.Interior.ColorIndex = myc
> Next
> End Sub
>
> --
> Don Guillett
> Microsoft MVPExcel
> SalesAid Software
> dguille...@austin.rr.com"Argus Rogue" <argusro...@satx.rr.com> wrote in message
>
> news:47ac36f0$0$30677$(E-Mail Removed)...
>
>
>
> > No I also have the following text
> > Pending
> > Broken
> > Running
> > Not Completed
>
> > Each Week I have to update this spreadsheet
>
> > * *A * * * * * * * * * *B * * * * * *C * * * * * * * *D * * * * * *E
> > 1 ENV * * * * * *02/01/08 * *02/08/08 * *02/15/08
> > 2 *Web * * * * * * *Pass * * * *Pass * * ** * *Broken
> > 3 *MFrame * * * *Pass * * * *Fail * * * * * * *Fixed
> > 4 GUI * * * * * * * *WIP * * * *Pending * * * *Running
> > 5
> > after our health check we go into this spreadsheet and update it. *I was
> > usingconditionalformattingbut I only could add threeconditional. *also
> > since they shared the workbook to everyone,conditionalformattingis no
> > longer working....
>
> > That is why i was trying to create amacrothat we could run that would
> > change the cell background and font *based on the text in the cell
>
> > Thanks for the help
>
> > Argus
>
> > "carlo" <carlo.ramu...@gmail.com> wrote in message
> >news:e68143a6-5cf5-4fb8-9714-(E-Mail Removed)...
> > On Feb 8, 3:02 pm, "Argus Rogue" <argusro...@satx.rr.com> wrote:
> >> hello all,
>
> >> I was wondering if anyone could help me out with this. I want to be able
> >> to
> >> change the background and font color of any cell where text is equal toa
> >> specific text.
> >> Idonot know if I have the right syntax, but here goes
>
> >> If text = "Fail" then
> >> Range("A1:J19").Select
> >> With Selection.Interior
> >> .ColorIndex = 3
> >> .Pattern = xlSolid
> >> End With
> >> Selection.Font.ColorIndex = 2
> >> Selection.Font.Bold = True
> >> Else
> >> If text = "Pass" Then
> >> Range("A1:J19").Select
> >> Selection.Interior.ColorIndex = 5
> >> Selection.Font.ColorIndex = 2
> >> Selection.Font.Bold = False
> >> Else
> >> If Text = "WIP" Then
> >> Range("A1:J19").Select
> >> With Selection.Interior
> >> .ColorIndex = 10
> >> .Pattern = xlSolid
> >> End With
> >> Selection.Font.ColorIndex = 2
> >> Selection.Font.Bold = True
> >> End If
> >> End If
> >> End If
>
> >> Any and all help in the matte is greatly appreciated
>
> > How is [text] referenced?Doyou get it from a Cell, or is it in your
> > VBA-Process?
>
> > If you only need to distinguish between 3 statements (Fail, Pass and
> > WIP) then i would recommend "ConditionalFormatting"
> > You can select Range A1:J19, then goto Format -->Conditional
> >Formatting, there you choose "Formula is" and then you enter
> > =$A$20="Fail"
> > then you select the Pattern and everything, then you press Add... and
> >dothe same again for Pass and WIP.
> > You only have 3 possibilities...if you need more then we have to go
> > back to your VBA code.
> > (Look up "Select Case" and don't use selection, but we can check that
> > later)
>
> > Hth
> > Carlo- Hide quoted text -
>
> - Show quoted text -

it only colors or updates column "e". How can I get it to do the
whold sheet

On Feb 12, 12:44*pm, Argus <mrcr...@yahoo.com> wrote:
> On Feb 8, 11:34*am, "Don Guillett" <dguille...@austin.rr.com> wrote:
>
>
>
>
>
> > try
> > Sub colorcells()
> > mc = "e"
> > lr = Cells(Rows.Count, mc).End(xlUp).Row
> > For Each c In Range(Cells(2, mc), Cells(lr, mc))
> > Select Case UCase(c)
> > Case "PENDING": myc = 5
> > Case "BROKEN": myc = 36
> > Case "RUNNING": myc = 8
> > 'Not Completed
> > 'as many as you need
> > Case Else
> > End Select
> > c.Interior.ColorIndex = myc
> > Next
> > End Sub
>
> > --
> > Don Guillett
> > Microsoft MVPExcel
> > SalesAid Software
> > dguille...@austin.rr.com"Argus Rogue" <argusro...@satx.rr.com> wrote in message
>
> >news:47ac36f0$0$30677$(E-Mail Removed)...
>
> > > No I also have the following text
> > > Pending
> > > Broken
> > > Running
> > > Not Completed
>
> > > Each Week I have to update this spreadsheet
>
> > > * *A * * * * * * * * * *B * * * * * *C * * * * * * * *D * * * * * *E
> > > 1 ENV * * * * * *02/01/08 * *02/08/08 * *02/15/08
> > > 2 *Web * * * * * * *Pass * * * *Pass * * * * * *Broken
> > > 3 *MFrame * * * *Pass * * * *Fail * * * * * * *Fixed
> > > 4 GUI * * * * * * * *WIP * * * *Pending * * * *Running
> > > 5
> > > after our health check we go into this spreadsheet and update it. *Iwas
> > > usingconditionalformattingbut I only could add threeconditional. *also
> > > since they shared the workbook to everyone,conditionalformattingis no
> > > longer working....
>
> > > That is why i was trying to create amacrothat we could run that would
> > > change the cell background and font *based on the text in the cell
>
> > > Thanks for the help
>
> > > Argus
>
> > > "carlo" <carlo.ramu...@gmail.com> wrote in message
> > >news:e68143a6-5cf5-4fb8-9714-(E-Mail Removed)....
> > > On Feb 8, 3:02 pm, "Argus Rogue" <argusro...@satx.rr.com> wrote:
> > >> hello all,
>
> > >> I was wondering if anyone could help me out with this. I want to be able
> > >> to
> > >> change the background and font color of any cell where text is equal to a
> > >> specific text.
> > >> Idonot know if I have the right syntax, but here goes
>
> > >> If text = "Fail" then
> > >> Range("A1:J19").Select
> > >> With Selection.Interior
> > >> .ColorIndex = 3
> > >> .Pattern = xlSolid
> > >> End With
> > >> Selection.Font.ColorIndex = 2
> > >> Selection.Font.Bold = True
> > >> Else
> > >> If text = "Pass" Then
> > >> Range("A1:J19").Select
> > >> Selection.Interior.ColorIndex = 5
> > >> Selection.Font.ColorIndex = 2
> > >> Selection.Font.Bold = False
> > >> Else
> > >> If Text = "WIP" Then
> > >> Range("A1:J19").Select
> > >> With Selection.Interior
> > >> .ColorIndex = 10
> > >> .Pattern = xlSolid
> > >> End With
> > >> Selection.Font.ColorIndex = 2
> > >> Selection.Font.Bold = True
> > >> End If
> > >> End If
> > >> End If
>
> > >> Any and all help in the matte is greatly appreciated
>
> > > How is [text] referenced?Doyou get it from a Cell, or is it in your
> > > VBA-Process?
>
> > > If you only need to distinguish between 3 statements (Fail, Pass and
> > > WIP) then i would recommend "ConditionalFormatting"
> > > You can select Range A1:J19, then goto Format -->Conditional
> > >Formatting, there you choose "Formula is" and then you enter
> > > =$A$20="Fail"
> > > then you select the Pattern and everything, then you press Add... and
> > >dothe same again for Pass and WIP.
> > > You only have 3 possibilities...if you need more then we have to go
> > > back to your VBA code.
> > > (Look up "Select Case" and don't use selection, but we can check that
> > > later)
>
> > > Hth
> > > Carlo- Hide quoted text -
>
> > - Show quoted text -
>
> it only colors or updates column "e". *How can I get it to do the
> whold sheet- Hide quoted text -
>
> - Show quoted text -

You could add following part to the sub:

Sub colorcells()
mc = "e"
lr = Cells(Rows.Count, mc).End(xlUp).Row
For Each c In Range(Cells(2, mc), Cells(lr, mc))
Select Case UCase(c)
Case "PENDING": myc = 5
Case "BROKEN": myc = 36
Case "RUNNING": myc = 8
'Not Completed
'as many as you need
Case Else
End Select
c.entirerow.Interior.ColorIndex = myc
Next
End Sub

hth

Carlo

Sub colorALLcells()
'lr = Cells(Rows.Count, "a").End(xlUp).Row
'For Each c In Range(Cells(2, "b"), Cells(lr, "d"))
For Each c In ActiveSheet.UsedRange
Select Case UCase(c)
Case "PENDING": myc = 5
Case "BROKEN": myc = 36
Case "RUNNING": myc = 8
'Not Completed
'as many as you need
Case Else
myc = 0
End Select
c.Interior.ColorIndex = myc
Next
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Argus" <(E-Mail Removed)> wrote in message
news:10de6d9d-ce11-41da-bf46-(E-Mail Removed)...
On Feb 8, 11:34 am, "Don Guillett" <dguille...@austin.rr.com> wrote:
> try
> Sub colorcells()
> mc = "e"
> lr = Cells(Rows.Count, mc).End(xlUp).Row
> For Each c In Range(Cells(2, mc), Cells(lr, mc))
> Select Case UCase(c)
> Case "PENDING": myc = 5
> Case "BROKEN": myc = 36
> Case "RUNNING": myc = 8
> 'Not Completed
> 'as many as you need
> Case Else
> End Select
> c.Interior.ColorIndex = myc
> Next
> End Sub
>
> --
> Don Guillett
> Microsoft MVPExcel
> SalesAid Software
> dguille...@austin.rr.com"Argus Rogue" <argusro...@satx.rr.com> wrote in
> message
>
> news:47ac36f0$0$30677$(E-Mail Removed)...
>
>
>
> > No I also have the following text
> > Pending
> > Broken
> > Running
> > Not Completed
>
> > Each Week I have to update this spreadsheet
>
> > A B C D E
> > 1 ENV 02/01/08 02/08/08 02/15/08
> > 2 Web Pass Pass Broken
> > 3 MFrame Pass Fail Fixed
> > 4 GUI WIP Pending Running
> > 5
> > after our health check we go into this spreadsheet and update it. I was
> > usingconditionalformattingbut I only could add threeconditional. also
> > since they shared the workbook to everyone,conditionalformattingis no
> > longer working....
>
> > That is why i was trying to create amacrothat we could run that would
> > change the cell background and font based on the text in the cell
>
> > Thanks for the help
>
> > Argus
>
> > "carlo" <carlo.ramu...@gmail.com> wrote in message
> >news:e68143a6-5cf5-4fb8-9714-(E-Mail Removed)...
> > On Feb 8, 3:02 pm, "Argus Rogue" <argusro...@satx.rr.com> wrote:
> >> hello all,
>
> >> I was wondering if anyone could help me out with this. I want to be
> >> able
> >> to
> >> change the background and font color of any cell where text is equal to
> >> a
> >> specific text.
> >> Idonot know if I have the right syntax, but here goes
>
> >> If text = "Fail" then
> >> Range("A1:J19").Select
> >> With Selection.Interior
> >> .ColorIndex = 3
> >> .Pattern = xlSolid
> >> End With
> >> Selection.Font.ColorIndex = 2
> >> Selection.Font.Bold = True
> >> Else
> >> If text = "Pass" Then
> >> Range("A1:J19").Select
> >> Selection.Interior.ColorIndex = 5
> >> Selection.Font.ColorIndex = 2
> >> Selection.Font.Bold = False
> >> Else
> >> If Text = "WIP" Then
> >> Range("A1:J19").Select
> >> With Selection.Interior
> >> .ColorIndex = 10
> >> .Pattern = xlSolid
> >> End With
> >> Selection.Font.ColorIndex = 2
> >> Selection.Font.Bold = True
> >> End If
> >> End If
> >> End If
>
> >> Any and all help in the matte is greatly appreciated
>
> > How is [text] referenced?Doyou get it from a Cell, or is it in your
> > VBA-Process?
>
> > If you only need to distinguish between 3 statements (Fail, Pass and
> > WIP) then i would recommend "ConditionalFormatting"
> > You can select Range A1:J19, then goto Format -->Conditional
> >Formatting, there you choose "Formula is" and then you enter
> > =$A$20="Fail"
> > then you select the Pattern and everything, then you press Add... and
> >dothe same again for Pass and WIP.
> > You only have 3 possibilities...if you need more then we have to go
> > back to your VBA code.
> > (Look up "Select Case" and don't use selection, but we can check that
> > later)
>
> > Hth
> > Carlo- Hide quoted text -
>
> - Show quoted text -

it only colors or updates column "e". How can I get it to do the
whold sheet