Formatting using Macro

S

simplymidori

I have done some rework to my tool and the macro I have now no longer works
for my report.

I'm looking to see if someone can help me on a couple macros.

Macro 1

If column B contains "Tb"
Row background color BLUE , Text color WHITE and BOLD

If column B contains "Tc"
Row background color YELLOW

If column B contains "Td"
Text color BLACK and BOLD

__________________________

I'll then run another macro which I have to breakout the report into
multiple worksheets

__________________________

Macro 2

Run macro on ALL worksheets except for "TRACKER" worksheet
If column B contains "Total" insert 2 rows below.

Thank you in advance.
 
M

Mike H

Hi,

Right click your sheet tab, view code and paste this in and run it

Sub sonic()
LastRow = Cells(Cells.Rows.Count, "B").End(xlUp).Row
Set myrange = Range("B1:B" & LastRow) '<Change to suit
For Each c In myrange
Select Case c.Value
Case Is = "Tb"
icolor = 33
fcolor = 2
Case Is = "Tc"
icolor = 6
fcolor = xlAutomatic
Case Is = "Td"
icolor = 5
fcolor = 1
Case Else
icolor = xlNone
fcolor = xlAutomatic
End Select
c.EntireRow.Interior.ColorIndex = icolor
c.EntireRow.Font.ColorIndex = fcolor
Next
End Sub


Mike
 
M

Mike H

Hi,

Missed the second bit and then work took priority!! Try this which this time
goes in a module

Sub stantial()
Dim ws As Worksheet
Dim X As Long
For Each ws In ThisWorkbook.Worksheets
ws.Select
If ws.Name <> "Tracker" Then
lastrow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
For X = lastrow To 1 Step -1
If Cells(X, 2).Value = "Total" Then
Cells(X + 1, 1).EntireRow.Insert xlShiftDown
Cells(X + 1, 1).EntireRow.Insert xlShiftDown
End If
Next
End If
lastrow = 0
Next ws
End Sub
 
S

simplymidori

Thanks Mike

I have a row above it that is in color. Can you tweak this stantial to
insert nofill background?

Thanks so much!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top