Macro Formatting > Skip a Line

[shmoes]

Hey all,

I'm creating a live document with a number of users and I want to make it as seamless as possible.
The best way I can think to do this is to have a Macro to format a few rows when we have random users creating a new entry.
My problem is I don't know how to skip from Row 1 to Row 3 without reformatting Row 1 and 2.

The Macro Recorder only does the selected cell, so I've manually gone in to change it to activecell, but offset doesn't let me skip lines.
I'm sure the same logic will help me set the conditional formatting in there as well.

Here is What I have so far.. Thanks in advance for any help


Sub New_Safety_Item()
'
' New_Safety_Item Macro
'
' Keyboard Shortcut: Ctrl+q
'
Range(ActiveCell, ActiveCell.Offset(0, 2)).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.149998474074526
.PatternTintAndShade = 0
End With
Selection.Font.Bold = True
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range(ActiveCell, ActiveCell.Offset(2, 1)).Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Font.Bold = True
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark2
.TintAndShade = -0.249977111117893
.PatternTintAndShade = 0
End With
ActiveCell.FormulaR1C1 = "Item #"
ActiveCell.Offset(0, 1).FormulaR1C1 = "Issue"
ActiveCell.Offset(0, 2).FormulaR1C1 = "Status"
ActiveCell.Offset(2, 0).FormulaR1C1 = "Date"
ActiveCell.Offset(2, 1).FormulaR1C1 = "Actions"
ActiveCell.Offset(3, 0).FormulaR1C1 = "On Receipt"
With Selection
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("C2").Select
Selection.FormatConditions.Add Type:=xlTextString, String:="Monitor", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 5296274
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlTextString, String:="Closed", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlTextString, String:="Open", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorLight2
.TintAndShade = 0.599963377788629
End With
Selection.FormatConditions(1).StopIfTrue = False
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Font.Bold = True
End Sub

~AZ

 

[shmoes]

Here is an example of what the formatting should look like.. I should be able to do it from and Row on A column.

The conditional color formatting changes with the word

Red for Closed
Green for Monitor
Blue for Open

~AZ

 

[Andrzejek]

Do you have data in column A in every cell? So if you hit an empty cell you are done?

If so, try this (if data starts in cell A2):

Dim i As Integer
i = 2
Do While Range(“A” & i).Value <> ""
  Select Case Range("A" & i).Value
    Case "Closed"[green]
      'Make Cell "A" & i - RED[/green]
    Case "Monitor"[green]
      'Make Cell "A" & i - GREEN[/green]
    Case ...

  End Select
Loop

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[shmoes]

Thanks for the response, no the final product looks just like the photo, data is entered manually afterwards.

but the active cell will always start in column A but it will change rows everytime.

I want them to be able to select a row and press the shortcut and blamo the formatting is done




~AZ

 

[shmoes]

Okay so I couldn't figure out my original request above to copy formatting from a separate page to the current active cell.
So I went another route and resorted to adding the cell/row formatting to the top of each page and hiding the rows.
I was able to easily change it to copy the first 4 rows and paste to the active cell ..

Which is simple and looks like...

Sub New_Safety_Item()
'
' New_Safety_Item Macro
' Add New Safety Item
'
' Keyboard Shortcut: Ctrl+q
'
Range("A1:C4").Copy Destination:=Cells(ActiveCell.Row + 1, 1)
End Sub

Which works almost fine, except I realize now I need to insert the copied cells and shift down, not paste..

This is what the macro recorder gives me and I need to change the rows ("18:18") to activecell somehow.
but no matter what I do the active cell entry gives me an error...

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+p
'
    Rows("1:4").Select
    Selection.Copy
    Rows("18:18").Select
    Selection.Insert Shift:=xlDown
End Sub

~AZ

 

[PWD]

I'm by no means an expert but I think that once you've selected and copied a range, then the top left cell of that range will be the active cell. Those in the know on these forums will tell you to try to avoid SELECT as much as possible. It may be possible to set the row number of the desired destination and refer to that in your Insert statement - I don't have my accumulated code at my new job and can't remember the syntax.

Try posting a question on the VBA Visual Basic for Applications forum to get some great answers.

Good luck.

Many thanks,
D€$

 

[kjv1611]

As PWD pointed out, your ActiveCell isn't what you think it is, and so you're trying to paste what you copied on top of itself. No WONDER Excel complains.

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+p
'
    [highlight #FCE94F]Rows("1:4").[b]Select[/b][/highlight]
    Selection.Copy
    Rows("18:18").Select
    Selection.Insert Shift:=xlDown
End Sub

So Row("18:18") - there's no way to tell it to use just "ActiveCell", b/c the currently ActiveCell is Rows("1:4") - and that would be Selection moreso than ActiveCell anyway, b/c it's not just one cell.

Is the difference always going to be 17 rows from the first row, or else 14 rows from the last row of your previous selection? If so, you could use a Long or maybe Integer variable for the row number, and capture the row-number from the selection.row, then add however many to it to get the next row.

Sub Macro1()
[GREEN]'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+p
'[/GREEN]
[highlight #D3D7CF]    Dim wb as Workbook
    Dim ws as Worksheet
    Dim x As Long 'RowNumber

    Set wb = ActiveWorkbook
    Set ws = wb.ActiveSheet
[/highlight]    
    Rows("1:4").Select
    Selection.Copy

    x = Selection.Row + 17

[highlight #D3D7CF]    Rows(x & ":" & x).Select[/highlight]
[GREEN]'    Rows("18:18").Select[/GREEN]
[highlight #D3D7CF]    Selection.Insert Shift:=xlDown[/highlight]
End Sub

I've not tested this, and I don't remember offhand whether using Row there will take the leading or ending row (but I think it's the leading row). So you'd need to test that yourself and see if any semblance of it would work.

Or if you prefer to go back to your original, I do believe it's possible, but if the latter option that you changed to is what you want to go with, don't give up. I don't think from what I've read so far that it's a pipe dream. You just have to get it sorted out.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[shmoes]

Thanks for the replies, the in rows will always be different, 18 just happened to be the row which I selected when recording.
Which is why I was hoping to change the insert to the activecell.

Everything stays the same except the copied cells/rows I want inserted, seems like it should be easy but i'm just not experienced enough to know.

I've moved the thread to here Link


Thanks for your help


~AZ