title: Manipulating Data to find the Greater Than Value
body: <p>I need to sort data in a workbook that contains several sheets with just one macro, so far I have managed to record the steps that I need to remove the unnecessary information but then am having a problem when I have to specify different greater amounts.</p>
  
  <p>I need the macro to be able to do the following:</p>
  
  <ol>
  <li>Message box to prompt for the greater
  than amount to be specified</li>
  <li>Highlight the values that are greater than specified</li>
  </ol>
  
  <p>Current Code:</p>
  
  <pre><code>Sub Conditional_Formatting()
      Columns("J:J").Select
      ActiveWorkbook.Worksheets("GULP USD").AutoFilter.Sort.SortFields.Clear
      ActiveWorkbook.Worksheets("GULP USD").AutoFilter.Sort.SortFields.Add Key:= _
          Range("J1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
          xlSortNormal
      With ActiveWorkbook.Worksheets("GULP USD").AutoFilter.Sort
          .Header = xlYes
          .MatchCase = False
          .Orientation = xlTopToBottom
          .SortMethod = xlPinYin
          .Apply
      End With
      Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
          Formula1:="=250000"
      Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
      With Selection.FormatConditions(1).Interior
          .PatternColorIndex = xlAutomatic
          .ThemeColor = xlThemeColorAccent6
          .TintAndShade = 0
      End With
      Selection.FormatConditions(1).StopIfTrue = False
  End Sub
  </code></pre>
  
  <p>Unfortunately the data sheets contain various different lines of data and will require various greater than amounts per sheet so will need to have a way to run this without specifying a certain number of rows or a specific greater than value.</p>
  
  <p>Any help would be greatly appreciated.</p>