ExcelBanter - View Single Post - ANYTIME NEEDED MACRO FOR CONDITIONAL FORMATTING

keiji kounoike

You're welcome!

Keiji

Tree wrote:
YOU ARE AMAZING.. PERFECT! I will work on where I put it so I can have access
and if I need more help with that, I know where to come..
THANK YOU!!!!!!!!!!!!!!!!!!!

"keiji kounoike" <"kounoike A | T ma.Pik" wrote:

Try this one, though i don't know this is what you want.

Sub FormatTotalRevised()
Dim Tarea As Range
Dim lr As Long, lc As Long

Cells.Interior.ColorIndex = xlNone

lr = Cells.Find("*", , , , xlByRows, xlPrevious).Row
lc = Cells.Find("*", , , , xlByColumns, xlPrevious).Column

Set Tarea = Range(Cells(1, "A"), Cells(lr, lc))

For Each rng In Tarea.Rows
If Application.CountIf(rng, "*TOTAL*") 0 Then
Range(Cells(rng.Row, "A"), Cells(rng.Row, lc)) _
.Interior.ColorIndex = 3
End If
Next

End Sub

If you don't want to have personal.xls or Add-in, save this macro into
another book, for example, Mymacro.xls. First open this Mymacro.xls,
next open your data file in the same Excel. you could see the name of
the macro above as Mymacro.xls!FormatTotalRevised. select this one and run.

Keiji

Tree wrote:
QB stands for Quickbooks.. we export data quite frequently into Excel to
create reports. If I did a cleanup macro - I don't see how that would work as
the rows and columns to cleanup change with each export and circumstance and
I certainly don't know how to account for that?
Could not your macro be adjusted to compensate for any blank rows or
columns? It works if there are no blank rows or columns and we might want to
run it with blank rows or columns.. Basically, I would like the macro to be
available to run WHENEVER subtotals are in the sheet and so therefore if the
word total is in ANY cell regardless of if there are blank rows or columns..
Again, your help is greatly appreciated..

"keiji kounoike" <"kounoike A | T ma.Pik" wrote:

I don't know what QB is and can't understand "I didn't go through the
data and get rid of any blank cells". you seemed to be able to clean it
up manually, then you could write a macro with a same procedure. Run
that clean-up macro first, next run your format macro. I don't know how
you clean up your data, so i can't help you with this.

About using this macro from any workbooks, you can use personal.xls or
make Add-in file. How to make these, Look up the URL
http://www.rondebruin.nl/personal.htm in Ron's Excel Tips

Keiji

Tree wrote:
Thank you so much Keiji! This worked perfectly! Except when I tried it with a
QB export where I didn't go through the data and get rid of any blank cells..
and that may happen sometimes.. usually I will go through the exported data
and "clean it up" so that I can run the DATA SUBTOTAL feature in Excel..
When I did that and then run your macro, it worked BEAUTIFULLY! Can you help
with this problem? And also how do I get this macro "saved" so that I can use
it for ANY worksheet without having to copy it from Notepad and Insert as a
Module every time?
Again, thank you so much for your help!!!

"keiji kounoike" <"kounoike A | T ma.Pik" wrote:

Other way. Try this one.

Sub FormatTotaltest()
Dim Tarea As Range

Cells.Interior.ColorIndex = xlNone
Set Tarea = Range("A1").CurrentRegion

For Each rng In Tarea.Rows
If Application.CountIf(rng, "*TOTAL*") 0 Then
Application.Intersect(rng, Tarea). _
Interior.ColorIndex = 3
End If
Next

End Sub

Keiji

Tree wrote:
EXCEL 2007
Jacob helped me do this CF for a particular worksheet and it works great,
but I want to be able to "call" this into any worksheet whenever I need it..
we Use the Data Tab to run subtotals all the time - especially when we export
data from Quickbooks to modify reports in Excel..
I tried to Record the Macro in the Personal.xlb but that didn't go so well -
so any assistance from the experts would be so greatly appreciated..

1. Select cell A1. Then press (Ctrl+A) to select all cells
2. From menu FormatConditional Formatting
3. For Condition1Select 'Formula Is' and enter the below formula
=COUNTIF(1:1,"*TOTAL*")
4. Click Format ButtonPattern and select your color (say Red)
5. Hit OK
You can remove the * from the formula if you are looking for a whole cell
match.