Change Cell color with macro

  • Thread starter Thread starter Argus Rogue
  • Start date Start date
A

Argus Rogue

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
 
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
Click to expand...

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

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
Click to expand...

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 address removed)
Argus Rogue said:
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

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
Click to expand...

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
Click to expand...
 
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



No I also have the following text
Pending
Broken
Running
Not Completed
Click to expand...
Each Week I have to update this spreadsheet
Click to expand...
   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....
Click to expand...
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
Click to expand...
Thanks for the help
Click to expand...
How is [text] referenced?Doyou get it from a Cell, or is it in your
VBA-Process?
Click to expand...
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)
Click to expand...
Hth
Carlo- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

it only colors or updates column "e". How can I get it to do the
whold sheet
 
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
Click to expand...
--
Don Guillett
Microsoft MVPExcel
SalesAid Software
news:[email protected]...
Click to expand...
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
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 -
Click to expand...
Click to expand...
- Show quoted text -
Click to expand...

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

- Show quoted text -
Click to expand...

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 address removed)
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
message



No I also have the following text
Pending
Broken
Running
Not Completed
Click to expand...
Each Week I have to update this spreadsheet
Click to expand...
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....
Click to expand...
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
Click to expand...
Thanks for the help
Click to expand...
How is [text] referenced?Doyou get it from a Cell, or is it in your
VBA-Process?
Click to expand...
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)
Click to expand...
Hth
Carlo- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

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