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.

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

"simplymidori" wrote:

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.

Thanks Mike -

Any idea on the 2nd macro?

Macro 2

Run macro on ALL worksheets except for the worksheet labeled "TRACKER"

If column B contains "Total" insert 2 rows below.


"Mike H" wrote:

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

"simplymidori" wrote:

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.

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



"simplymidori" wrote:

Thanks Mike -

Any idea on the 2nd macro?

Macro 2

Run macro on ALL worksheets except for the worksheet labeled "TRACKER"

If column B contains "Total" insert 2 rows below.


"Mike H" wrote:

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

"simplymidori" wrote:

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.

Thanks Mike

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

Thanks so much!

"Mike H" wrote:

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



"simplymidori" wrote:

Thanks Mike -

Any idea on the 2nd macro?

Macro 2

Run macro on ALL worksheets except for the worksheet labeled "TRACKER"

If column B contains "Total" insert 2 rows below.


"Mike H" wrote:

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

"simplymidori" wrote:

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.