Run macro within selected cell range

  • I recorded the following macro for conditional formatting and it works great within the current selected cell range that starts at row 13. What do I need to add/change to run it depending on the selection since the range may vary? Thank you.

  • Re: Run macro withing selected cell range


    You may try something like this....


    Code
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=IF(COUNTA(" & Selection.Cells(1).Address(0, 2) & ")=1,MOD(SUBTOTAL(3," & Selection.Cells(1).Address(2, 0) & ":" & Selection.Cells(1).Address(0, 2) & "),3)=0,0)"
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorDark1
            .TintAndShade = -0.249946592608417
        End With
        Selection.FormatConditions(1).StopIfTrue = True
  • Re: Run macro withing selected cell range


    I understand it and appreciate your help. It works perfectly within any selected data range except that it started to change the colors of the columns in addition to rows. I'm not an expert and don't know which part of the formula changes the columns color. Thanks in advance for any suggestions/hints/direction.

  • Re: Run macro withing selected cell range


    See if this is what you are trying to achieve...

    Code
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
    "=IF(COUNTA(" & Selection.Cells(1).Address(0, 2) & ")=1,MOD(SUBTOTAL(3," & Selection.Cells(1).Address(1, 1) & ":" & Selection.Cells(1).Address(0, 2) & "),3)=0,0)"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.249946592608417
    End With
    Selection.FormatConditions(1).StopIfTrue = True

  • Hi Sir,


    I want to run macro for selected cell only. (Macro code mentioned below)


    please help me in run macro for selected cell.



    Sub Macro1()


    '


    ' Macro1 Macro


    '


    ' Keyboard Shortcut: Ctrl+w


    '


    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],[26.07.21.xlsx]Sheet1!C1:C5,2,0)"


    Range("G783").Select


    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC4,[26.07.21.xlsx]Sheet1!C1:C5,2,0)"


    Range("G783").Select


    Selection.Copy


    Range("H783").Select


    ActiveSheet.Paste


    Application.CutCopyMode = False


    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC4,[26.07.21.xlsx]Sheet1!C1:C5,3,0)"


    Range("G783:H783").Select


    Range("H783").Activate


    Selection.AutoFill Destination:=Range("G783:H811")


    Range("G783:H811").Select


    ActiveWindow.SmallScroll Down:=21


    Selection.Copy


    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _


    :=False, Transpose:=False


    Selection.Replace What:="#n/a", Replacement:="", LookAt:=xlPart, _


    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _


    ReplaceFormat:=False


    Application.CutCopyMode = False


    Range("G784:H810").Select


    Range("G810").Activate


    Selection.Borders(xlDiagonalDown).LineStyle = xlNone


    Selection.Borders(xlDiagonalUp).LineStyle = xlNone


    With Selection.Borders(xlEdgeLeft)


    .LineStyle = xlContinuous


    .ColorIndex = 0


    .TintAndShade = 0


    .Weight = xlThin


    End With


    With Selection.Borders(xlEdgeTop)


    .LineStyle = xlContinuous


    .ColorIndex = 0


    .TintAndShade = 0


    .Weight = xlThin


    End With


    With Selection.Borders(xlEdgeBottom)


    .LineStyle = xlContinuous


    .ColorIndex = 0


    .TintAndShade = 0


    .Weight = xlThin


    End With


    With Selection.Borders(xlEdgeRight)


    .LineStyle = xlContinuous


    .ColorIndex = 0


    .TintAndShade = 0


    .Weight = xlThin


    End With


    With Selection.Borders(xlInsideVertical)


    .LineStyle = xlContinuous


    .ColorIndex = 0


    .TintAndShade = 0


    .Weight = xlThin


    End With


    With Selection.Borders(xlInsideHorizontal)


    .LineStyle = xlContinuous


    .ColorIndex = 0


    .TintAndShade = 0


    .Weight = xlThin


    End With


    Range("G784").Select


    End Sub

  • Vasu123.vp


    Welcome to the Forum. Please read the Forum Rules to understand how the Forum works . You have broken two rules already. Please start your own question


    All VBA code posted in the forum must be wrapped in code tags, which you omitted, including single-line code snippets.Be sure to use them in future posts.


    How to use code tags


    Just highlight all of the code and press the <> in the post menu above button to add the code tags.


    Please start your own post. Posting in another member's Thread is known as hijacking and is not allowed here. By all means add a link to a Thread that may be related to your question.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!