Macro Commands
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 |
Macro Commands
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 |
Macro Commands
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 |
Macro Commands
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 |
Macro Commands
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 |
Macro Commands
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 |
All times are GMT +1. The time now is 01:57 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com