I have a spreadsheet that changes from month-to-month. I have a Macro that
does different formatting and I wanted to do something odd. Column J has
nearly all dates, however, periodically there is text. Either "Billing: #" or
"Complete: #". If "Billing" or "Complete" shows up I want that entire row to
be formatted differently. The default format is center, if this text appears
I want the format to be, ".HorizontalAlignment = xlGeneral" and
".VerticalAlignment = xlTop".

Does this makes sense? Is it possible?

Quick code that I didn't try. You would need to add this in your formatting
macro.

with activesheet.range("J1").entirecolumn.specialcells( xlCellTypeConstants, 2)
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlTop
end with

This is not really efficient code but it gives you an idea. For this to
work, you need to have all your data in the sheet. To have this work like
conditional formatting, you need to write your in code in the worksheet
change event.

p-a

"FrankM" wrote:

I have a spreadsheet that changes from month-to-month. I have a Macro that
does different formatting and I wanted to do something odd. Column J has
nearly all dates, however, periodically there is text. Either "Billing: #" or
"Complete: #". If "Billing" or "Complete" shows up I want that entire row to
be formatted differently. The default format is center, if this text appears
I want the format to be, ".HorizontalAlignment = xlGeneral" and
".VerticalAlignment = xlTop".

Does this makes sense? Is it possible?

That is very close. it formats the column but what I want to format is the
row. I want to scan down column J and when Billing or Complete shows up in
the column, I want the entire row that Billing or Complete shows up in to be
formatted.

"PA" wrote:

Quick code that I didn't try. You would need to add this in your formatting
macro.

with activesheet.range("J1").entirecolumn.specialcells( xlCellTypeConstants, 2)
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlTop
end with

This is not really efficient code but it gives you an idea. For this to
work, you need to have all your data in the sheet. To have this work like
conditional formatting, you need to write your in code in the worksheet
change event.

p-a

"FrankM" wrote:

I have a spreadsheet that changes from month-to-month. I have a Macro that
does different formatting and I wanted to do something odd. Column J has
nearly all dates, however, periodically there is text. Either "Billing: #" or
"Complete: #". If "Billing" or "Complete" shows up I want that entire row to
be formatted differently. The default format is center, if this text appears
I want the format to be, ".HorizontalAlignment = xlGeneral" and
".VerticalAlignment = xlTop".

Does this makes sense? Is it possible?

Your right. Add

..EntireRow

at the end:

With ActiveSheet.Range("J1").EntireColumn.SpecialCells( xlCellTypeConstants,
2).EntireRow
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlTop
End With

"FrankM" wrote:

That is very close. it formats the column but what I want to format is the
row. I want to scan down column J and when Billing or Complete shows up in
the column, I want the entire row that Billing or Complete shows up in to be
formatted.

"PA" wrote:

Quick code that I didn't try. You would need to add this in your formatting
macro.

with activesheet.range("J1").entirecolumn.specialcells( xlCellTypeConstants, 2)
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlTop
end with

This is not really efficient code but it gives you an idea. For this to
work, you need to have all your data in the sheet. To have this work like
conditional formatting, you need to write your in code in the worksheet
change event.

p-a

"FrankM" wrote:

I have a spreadsheet that changes from month-to-month. I have a Macro that
does different formatting and I wanted to do something odd. Column J has
nearly all dates, however, periodically there is text. Either "Billing: #" or
"Complete: #". If "Billing" or "Complete" shows up I want that entire row to
be formatted differently. The default format is center, if this text appears
I want the format to be, ".HorizontalAlignment = xlGeneral" and
".VerticalAlignment = xlTop".

Does this makes sense? Is it possible?

Thank you. It worked perfectly. I am very grateful.

"PA" wrote:

Your right. Add

.EntireRow

at the end:

With ActiveSheet.Range("J1").EntireColumn.SpecialCells( xlCellTypeConstants,
2).EntireRow
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlTop
End With

"FrankM" wrote:

That is very close. it formats the column but what I want to format is the
row. I want to scan down column J and when Billing or Complete shows up in
the column, I want the entire row that Billing or Complete shows up in to be
formatted.

"PA" wrote:

Quick code that I didn't try. You would need to add this in your formatting
macro.

with activesheet.range("J1").entirecolumn.specialcells( xlCellTypeConstants, 2)
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlTop
end with

This is not really efficient code but it gives you an idea. For this to
work, you need to have all your data in the sheet. To have this work like
conditional formatting, you need to write your in code in the worksheet
change event.

p-a

"FrankM" wrote:

I have a spreadsheet that changes from month-to-month. I have a Macro that
does different formatting and I wanted to do something odd. Column J has
nearly all dates, however, periodically there is text. Either "Billing: #" or
"Complete: #". If "Billing" or "Complete" shows up I want that entire row to
be formatted differently. The default format is center, if this text appears
I want the format to be, ".HorizontalAlignment = xlGeneral" and
".VerticalAlignment = xlTop".

Does this makes sense? Is it possible?