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

    Regards.
    sktneer

    Re: Run macro withing selected cell range


    Thank you, sktneer_1,


    but this change also highlights the every 3rd column. I need to highlight only every 3rd row, not columns. Any thoughts?

    Re: Run macro withing selected cell range


    I only applied your formula in the code depending upon the selected range.
    If it's not producing the desired output, please check your formula.

    Regards.
    sktneer

    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


    One more thing- I'm using it for the table with hidden cells to change the color of every third row of active data.

    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

    Regards.
    sktneer

    Re: Run macro withing selected cell range


    It works great, and this is exactly what I need. Thank you so much for your help!


    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!

Register Yourself Login