We're aware that some users are experiencing technical issues which the team are working to resolve. See the Community Noticeboard for more info. Thank you for your patience.
📨 Have you signed up to the Forum's new Email Digest yet? Get a selection of trending threads sent straight to your inbox daily, weekly or monthly!

Macro Query

Options
Cotta
Cotta Posts: 3,667 Forumite
Hi All,


I am building some macros to automate tasks I perform on Excel.


I currently need to sort column A and any column with the word "Restricted" within it should be highlight red. I am able to perform this on the current dataset as when mapping out the process I highlighted all such cells. However this leaves the code looking for a specific number of rows rather than what is in the cells.


Is it possible to do this based on what the value of column A is?

Comments

  • grumbler
    grumbler Posts: 58,629 Forumite
    Part of the Furniture 10,000 Posts Name Dropper Photogenic
    Why a macro, not a conditional formatting?
  • Le_Kirk
    Le_Kirk Posts: 24,400 Forumite
    Part of the Furniture 10,000 Posts Photogenic Name Dropper
    Why do you need to SORT items in one column? Do you need to order them from top to bottom in alphabetical order? Or do you just want to highlight any cell in range containing the value "restricted"? As suggested by grumbler, just use conditional formatting. Do you mean Macro or do you want to write some vba code? If the latter, I have always found the following site Excellent. See what I did there?
    http://www.mrexcel.com/forum/excel-questions/604428-how-sort-data-columns-visual-basic-applications-instead-excels-sort-function.html
  • Try


    Sub Macro3()
    '
    ' Macro3 Macro
    '

    '
    Range("A1").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
    Formula1:="=""restricted"""
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
    .Color = -16383844
    .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .Color = 13551615
    .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    End Sub
This discussion has been closed.
Meet your Ambassadors

🚀 Getting Started

Hi new member!

Our Getting Started Guide will help you get the most out of the Forum

Categories

  • All Categories
  • 350.7K Banking & Borrowing
  • 253K Reduce Debt & Boost Income
  • 453.4K Spending & Discounts
  • 243.7K Work, Benefits & Business
  • 598.5K Mortgages, Homes & Bills
  • 176.8K Life & Family
  • 256.9K Travel & Transport
  • 1.5M Hobbies & Leisure
  • 16.1K Discuss & Feedback
  • 37.6K Read-Only Boards

Is this how you want to be seen?

We see you are using a default avatar. It takes only a few seconds to pick a picture.