Hello,

I am trying to create a macro that will custom format cells within a
range I have selected based on the contents of the cells. I get some
rather ugly output from a database in excel, and want to make it a bit
more readable (make specific rows different formats to show sub-totals
etc). All of my formatting will be based on the left-most cell (for
example, if the row starts with a *, then I want all of the data in the
range I select to be bold).

I would just use conditional formatting, but the end goal is to be able
to use this on any range of data quickly, conditional formatting cannot
be applied like that. I plan to make this so whenever I hit some key
combination yet to be decided it runs on my selected data and formats
as such.

Any ideas as to how I could go about doing this would be greatly
appreciated.

Lance

Lance,

Based on info below, here's an example of code to format text as bold &
red based on each cell in a selected range. You have to change/add
format conditions as needed as well as resize the columns as needed.
Not sure how many conditions you are testing.

Alan

Sub TestData2()
Dim MyCell As Range
For Each MyCell In Selection
If Left(MyCell.Value, 2) = "a" Then
With MyCell.Resize(1, 10).Font
.Bold = True
.ColorIndex = 3
End With
End If
Next MyCell
End Sub

wrote:
Hello,

I am trying to create a macro that will custom format cells within a
range I have selected based on the contents of the cells. I get some
rather ugly output from a database in excel, and want to make it a bit
more readable (make specific rows different formats to show sub-totals
etc). All of my formatting will be based on the left-most cell (for
example, if the row starts with a *, then I want all of the data in the
range I select to be bold).

I would just use conditional formatting, but the end goal is to be able
to use this on any range of data quickly, conditional formatting cannot
be applied like that. I plan to make this so whenever I hit some key
combination yet to be decided it runs on my selected data and formats
as such.

Any ideas as to how I could go about doing this would be greatly
appreciated.

Lance

Alan,

Your sample code is exactly what I'm after - except is there a way to
do it so that instead of formatting MyCell cased on the condition, I
can format MyCellRow based on the condition? You will have to forgive
me, I am familiar with the concepts of VBA, but I am still very new to
VBA in Excel (I've done all my previous work in access).

Thanks for your help,

Lance
wrote:
Lance,

Based on info below, here's an example of code to format text as bold &
red based on each cell in a selected range. You have to change/add
format conditions as needed as well as resize the columns as needed.
Not sure how many conditions you are testing.

Alan

Sub TestData2()
Dim MyCell As Range
For Each MyCell In Selection
If Left(MyCell.Value, 2) = "a" Then
With MyCell.Resize(1, 10).Font
.Bold = True
.ColorIndex = 3
End With
End If
Next MyCell
End Sub

wrote:
Hello,

I am trying to create a macro that will custom format cells within a
range I have selected based on the contents of the cells. I get some
rather ugly output from a database in excel, and want to make it a bit
more readable (make specific rows different formats to show sub-totals
etc). All of my formatting will be based on the left-most cell (for
example, if the row starts with a *, then I want all of the data in the
range I select to be bold).

I would just use conditional formatting, but the end goal is to be able
to use this on any range of data quickly, conditional formatting cannot
be applied like that. I plan to make this so whenever I hit some key
combination yet to be decided it runs on my selected data and formats
as such.

Any ideas as to how I could go about doing this would be greatly
appreciated.

Lance

try using "EntireRow" ie.
MyCell.EntireRow.Interior.ColorIndex = 3 (or what ever format you'd
like to perform

Good luck,
Sandy

wrote:
Alan,

Your sample code is exactly what I'm after - except is there a way to
do it so that instead of formatting MyCell cased on the condition, I
can format MyCellRow based on the condition? You will have to forgive
me, I am familiar with the concepts of VBA, but I am still very new to
VBA in Excel (I've done all my previous work in access).

Thanks for your help,

Lance
wrote:
Lance,

Based on info below, here's an example of code to format text as bold &
red based on each cell in a selected range. You have to change/add
format conditions as needed as well as resize the columns as needed.
Not sure how many conditions you are testing.

Alan

Sub TestData2()
Dim MyCell As Range
For Each MyCell In Selection
If Left(MyCell.Value, 2) = "a" Then
With MyCell.Resize(1, 10).Font
.Bold = True
.ColorIndex = 3
End With
End If
Next MyCell
End Sub

wrote:
Hello,

I am trying to create a macro that will custom format cells within a
range I have selected based on the contents of the cells. I get some
rather ugly output from a database in excel, and want to make it a bit
more readable (make specific rows different formats to show sub-totals
etc). All of my formatting will be based on the left-most cell (for
example, if the row starts with a *, then I want all of the data in the
range I select to be bold).

I would just use conditional formatting, but the end goal is to be able
to use this on any range of data quickly, conditional formatting cannot
be applied like that. I plan to make this so whenever I hit some key
combination yet to be decided it runs on my selected data and formats
as such.

Any ideas as to how I could go about doing this would be greatly
appreciated.

Lance

I realize my response was slightly deficient.

I'd like it to format the entire row, within the selection - I have
been able to make it do the entire row - but I'd like to not change the
format outside the selection

Sandy wrote:
try using "EntireRow" ie.
MyCell.EntireRow.Interior.ColorIndex = 3 (or what ever format you'd
like to perform

Good luck,
Sandy

wrote:
Alan,

Your sample code is exactly what I'm after - except is there a way to
do it so that instead of formatting MyCell cased on the condition, I
can format MyCellRow based on the condition? You will have to forgive
me, I am familiar with the concepts of VBA, but I am still very new to
VBA in Excel (I've done all my previous work in access).

Thanks for your help,

Lance
wrote:
Lance,

Based on info below, here's an example of code to format text as bold &
red based on each cell in a selected range. You have to change/add
format conditions as needed as well as resize the columns as needed.
Not sure how many conditions you are testing.

Alan

Sub TestData2()
Dim MyCell As Range
For Each MyCell In Selection
If Left(MyCell.Value, 2) = "a" Then
With MyCell.Resize(1, 10).Font
.Bold = True
.ColorIndex = 3
End With
End If
Next MyCell
End Sub

wrote:
Hello,

I am trying to create a macro that will custom format cells within a
range I have selected based on the contents of the cells. I get some
rather ugly output from a database in excel, and want to make it a bit
more readable (make specific rows different formats to show sub-totals
etc). All of my formatting will be based on the left-most cell (for
example, if the row starts with a *, then I want all of the data in the
range I select to be bold).

I would just use conditional formatting, but the end goal is to be able
to use this on any range of data quickly, conditional formatting cannot
be applied like that. I plan to make this so whenever I hit some key
combination yet to be decided it runs on my selected data and formats
as such.

Any ideas as to how I could go about doing this would be greatly
appreciated.

Lance

Does this help???

Sub SelectionRowFormat()
Dim MyCell As Object
For Each MyCell In Selection
If MyCell = "3" Then

ActiveWindow.RangeSelection.Rows(MyCell.Row).Inter ior.ColorIndex = 3
End If
Next MyCell
End Sub


Sandy


wrote:
I realize my response was slightly deficient.

I'd like it to format the entire row, within the selection - I have
been able to make it do the entire row - but I'd like to not change the
format outside the selection

Sandy wrote:
try using "EntireRow" ie.
MyCell.EntireRow.Interior.ColorIndex = 3 (or what ever format you'd
like to perform

Good luck,
Sandy

wrote:
Alan,

Your sample code is exactly what I'm after - except is there a way to
do it so that instead of formatting MyCell cased on the condition, I
can format MyCellRow based on the condition? You will have to forgive
me, I am familiar with the concepts of VBA, but I am still very new to
VBA in Excel (I've done all my previous work in access).

Thanks for your help,

Lance
wrote:
Lance,

Based on info below, here's an example of code to format text as bold &
red based on each cell in a selected range. You have to change/add
format conditions as needed as well as resize the columns as needed.
Not sure how many conditions you are testing.

Alan

Sub TestData2()
Dim MyCell As Range
For Each MyCell In Selection
If Left(MyCell.Value, 2) = "a" Then
With MyCell.Resize(1, 10).Font
.Bold = True
.ColorIndex = 3
End With
End If
Next MyCell
End Sub

wrote:
Hello,

I am trying to create a macro that will custom format cells within a
range I have selected based on the contents of the cells. I get some
rather ugly output from a database in excel, and want to make it a bit
more readable (make specific rows different formats to show sub-totals
etc). All of my formatting will be based on the left-most cell (for
example, if the row starts with a *, then I want all of the data in the
range I select to be bold).

I would just use conditional formatting, but the end goal is to be able
to use this on any range of data quickly, conditional formatting cannot
be applied like that. I plan to make this so whenever I hit some key
combination yet to be decided it runs on my selected data and formats
as such.

Any ideas as to how I could go about doing this would be greatly
appreciated.

Lance