Macro Commands

[danh]

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

[kassie]

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

[danh]

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

[DCSwearingen]

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]

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

[danh]

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
>
>