I need to read through each row in an Excel Worksheet and format it depending
on a code in Col G. If Code is B then the Cell in Col A must be bold. If
the Code is W, then whole row when there is data must be Font Color White and
Fill Color Purple.
I don't know how to make macro commands read each row at a time until it
reaches the last row and format it accordingly. I suppose it must be in a
loop. Can somebody help me?
Thanks,
Danielle

Have you tried conditional formatting? Say your code is in G1. Click on
Format|Conditional Format, click the down arrow next to Cell Value, select
Formula is, and type in =$G1="B" do formatting, click on Add, enter =$G1="W"
and do formatting. Use the painter to format the rest of the row, or else
select the row, and then do conditional formatting. Use the painter to
format the other rows. That means you do not have to run a macro.

"danh" wrote:

I need to read through each row in an Excel Worksheet and format it depending
on a code in Col G. If Code is B then the Cell in Col A must be bold. If
the Code is W, then whole row when there is data must be Font Color White and
Fill Color Purple.
I don't know how to make macro commands read each row at a time until it
reaches the last row and format it accordingly. I suppose it must be in a
loop. Can somebody help me?
Thanks,
Danielle

Thanks Kassie for your help. As you say I can use the Conditional Formatting
but unfortunately the spreadsheet I need to format comes from another system
and is exported to excel. So the spreadsheet always changes. I have to run
a macro command to see how many rows there are in the spreadsheet and then
format it according the the code in Col G. It is the only col. that will
always contain the code.
Can somebody help me?
Thanks,
Danielle

"kassie" wrote:

Have you tried conditional formatting? Say your code is in G1. Click on
Format|Conditional Format, click the down arrow next to Cell Value, select
Formula is, and type in =$G1="B" do formatting, click on Add, enter =$G1="W"
and do formatting. Use the painter to format the rest of the row, or else
select the row, and then do conditional formatting. Use the painter to
format the other rows. That means you do not have to run a macro.

"danh" wrote:

I need to read through each row in an Excel Worksheet and format it depending
on a code in Col G. If Code is B then the Cell in Col A must be bold. If
the Code is W, then whole row when there is data must be Font Color White and
Fill Color Purple.
I don't know how to make macro commands read each row at a time until it
reaches the last row and format it accordingly. I suppose it must be in a
loop. Can somebody help me?
Thanks,
Danielle

I know this has been posted for several days, but if you still need an
idea try this.

First, select all cells by clicking in the small gray box to the left
of column A and just above row 1. Pick Format Conditional Format and
select Formula Is and enter =$G1="W" along with the formatting for
Purple cell with white text as mentioned earlier by Kassie.

Next, select the entire column A by clicking on the column header A.
Pick Format Conditional Format and select Formula Is and enter
=$G1="B" along with the formatting for bold text, again, as mentioned
earlier by Kassie.

The first step will format all cells within the same row that contains
the "W". the second step will format text as bold for cells in column A
only if the cell in column G, in the same row, contains the "B".


--
DCSwearingen

Getting old, but love computers.
------------------------------------------------------------------------
DCSwearingen's Profile: http://www.excelforum.com/member.php...o&userid=21506
View this thread: http://www.excelforum.com/showthread...hreadid=552671

Try this:
'------------------
Option Explicit
'------------------
Private Function FindLastCell()
Dim lCell As String
Range("A1").Select
lCell = ActiveCell.SpecialCells(xlLastCell).Address
FindLastCell = lCell
End Function
'------------------
Private Sub CondFormat()
Dim cFormat As String, cCell As String, tRange As String, c As Range
Dim lCell As String, lRow As Long, lCol As Long
lCell = FindLastCell
lRow = Range(lCell).Row
lCol = Range(lCell).Column
tRange = "$A$1:" & lCell
For Each c In Range(tRange)
cFormat = "=$G$" & c.Row & "=""W"""
c.FormatConditions.Add Type:=xlExpression, Formula1:=cFormat
c.FormatConditions(1).Font.ColorIndex = 2
c.FormatConditions(1).Interior.ColorIndex = 13
Next
tRange = Cells(lRow, 1).Address
tRange = "$A$1:" & tRange
For Each c In Range(tRange)
cFormat = "=$G$" & c.Row & "=""B"""
c.FormatConditions.Add Type:=xlExpression, Formula1:=cFormat
c.FormatConditions(2).Font.Bold = True
Next
End Sub


--
DCSwearingen

Getting old, but love computers.
------------------------------------------------------------------------
DCSwearingen's Profile: http://www.excelforum.com/member.php...o&userid=21506
View this thread: http://www.excelforum.com/showthread...hreadid=552671

Thanks for your help. I will try your VBA Code as it seems to be exactly
what I need
Danielle

"DCSwearingen" wrote:


Try this:
'------------------
Option Explicit
'------------------
Private Function FindLastCell()
Dim lCell As String
Range("A1").Select
lCell = ActiveCell.SpecialCells(xlLastCell).Address
FindLastCell = lCell
End Function
'------------------
Private Sub CondFormat()
Dim cFormat As String, cCell As String, tRange As String, c As Range
Dim lCell As String, lRow As Long, lCol As Long
lCell = FindLastCell
lRow = Range(lCell).Row
lCol = Range(lCell).Column
tRange = "$A$1:" & lCell
For Each c In Range(tRange)
cFormat = "=$G$" & c.Row & "=""W"""
c.FormatConditions.Add Type:=xlExpression, Formula1:=cFormat
c.FormatConditions(1).Font.ColorIndex = 2
c.FormatConditions(1).Interior.ColorIndex = 13
Next
tRange = Cells(lRow, 1).Address
tRange = "$A$1:" & tRange
For Each c In Range(tRange)
cFormat = "=$G$" & c.Row & "=""B"""
c.FormatConditions.Add Type:=xlExpression, Formula1:=cFormat
c.FormatConditions(2).Font.Bold = True
Next
End Sub


--
DCSwearingen

Getting old, but love computers.
------------------------------------------------------------------------
DCSwearingen's Profile: http://www.excelforum.com/member.php...o&userid=21506
View this thread: http://www.excelforum.com/showthread...hreadid=552671