Setting up a Macro to run on a highlighted area

[bchemist]

Hello,
The computer system my company uses exports to excel in an ugly and unusable way. Normally i manually sort the data but I recently took an excel course and learned a bit about macro's. I have a macro set up to auto format the data but i was wondering if it can be set up to run on a highlighted area? the data changes each time it is exported and there is other data in the same report that needs to be formatted differently to be usable.
here is what i have so far.

    Range("A2").Select
    ActiveCell.FormulaR1C1 = "=RC[4]*100"
    Range("A2").Select
    Selection.AutoFill Destination:=Range("A2:A42"), Type:=xlFillDefault
    Range("A2:A42").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("E2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "100"
    Selection.AutoFill Destination:=Range("E2:E42"), Type:=xlFillDefault
    Range("E2:E42").Select
    Selection.NumberFormat = "#,##0.000"
    Selection.NumberFormat = "#,##0.00"
    Selection.NumberFormat = "#,##0.0"
    Selection.NumberFormat = "#,##0"
    Range("D1:D42").Select
    Selection.Cut
    Range("C1").Select
    ActiveSheet.Paste
    Columns("C:C").EntireColumn.AutoFit
    Columns("D:D").ColumnWidth = 41.14
End Sub
Sub formatbreakouts()
'
' formatbreakouts Macro
' Formatting ingredient breakouts
'


'
    Columns("C:C").EntireColumn.AutoFit
    Columns("D:D").EntireColumn.AutoFit
    Range("E2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=RC[-3]*100"
    Range("E2").Select
    Selection.AutoFill Destination:=Range("E2:E42"), Type:=xlFillDefault
    Range("E2:E42").Select
    ActiveWindow.SmallScroll Down:=-21
    Range("F2").Select
    ActiveCell.FormulaR1C1 = "=lower"
    Range("F2").Select
    ActiveCell.FormulaR1C1 = "=LOWER(RC[-2]:R[18]C[-2])+"","""
    Range("F2").Select
    ActiveCell.FormulaR1C1 = "=LOWER(RC[-2])+"","""
    Range("F2").Select
    ActiveCell.FormulaR1C1 = "=LOWER(RC[-2])&"","""
    Range("F2").Select
    Selection.AutoFill Destination:=Range("F2:F42"), Type:=xlFillDefault
    Range("F2:F42").Select
    Selection.Cut
    Application.CutCopyMode = False
    Selection.Copy
    Range("D2").Select
End Sub

 

[JLGWhiz]

It depends on what you mean by 'run on highlighted'. If you are referring to the selected area which Excel shades, that is referenced in code as 'Selection'. If you are referring to the fill color of the cells, then you have to use the interior property of the cells to access the color. If you are referring to the font color, then you would use the 'Font' property to access the color. It also depends on what you want to do with the data as to how you would structure your code. I know this probably does not answer your question, but the question was kind of open end, so maybe you want to consider rephrasing the question.

 

[bchemist]

Thanks for responding. My excel lingo is lazy. I mean the selected area. The number of columns of the data never changes but sometimes there is more or less data. I guess another option would be for it to do the formatting until it hits a blank space starting at a selected cell. Otherwise I will need to move the data around on the spreadsheet to avoid formatting the wrong data set.

 

[JLGWhiz]

Thanks for responding. My excel lingo is lazy. I mean the selected area. The number of columns of the data never changes but sometimes there is more or less data. I guess another option would be for it to do the formatting until it hits a blank space starting at a selected cell. Otherwise I will need to move the data around on the spreadsheet to avoid formatting the wrong data set.

I neglected to welcome you to the forum, so please accept a belated welcome. Just a tip in posting your issues. If you can post a link to a share drive where your file can be viewed it will save a lot of narrative explanation about your data layout. A before and after example is always a good way to get your point across. While you cannot do attachments like email attachments, there are ways to post examples and you can get to those methods by clicking the word 'Attachments' in my signature blurb below. The more you use conventional Excel terminology, the better the results from respondents on the Forum.

 

[JLGWhiz]

To create a dynamic range in code (one that will change with addition or deletion of rows) you can put the range in a variable which will be evealuated each time the procedure runs.

Sub t()
Dim rng As Range, lr As Long 'rng will be your dynamic range, lr will be the last row number of the range
With ActiveSheet
 lr = .Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row 'searches from bottou upward for data
 Set rng = .Range("A1:F" & lr) 'Change F to whatever column your range goes to.
 MsgBox rng.Address
 'You can add code here to do stuff with the rng range.
End With
End Sub

 

[bchemist]

Okay, I will post an example document later today. I will try to set it up to show each step in the changes to show exactly what I am doing to it.

 

[JLGWhiz]

It is your original code cleaned up a little.

Range("A2").FormulaR1C1 = "=RC[4]*100"
    Range("A2").AutoFill Destination:=Range("A2:A42")
        With Range("A2:A42")
            .Value = Value
        End With
    Range("E2") = "100"
    Range("B2").AutoFill Destination:=Range("E2:E42")
    Range("E2:E42").NumberFormat = "#,##0"
    Range("D1:D42").Cut Range("C1")
    Columns("C:C").EntireColumn.AutoFit
    Columns("D:D").ColumnWidth = 41.14
End Sub

Not sure if the title line was the one below in Post #1 or not.