cell value as text string?

[kl_clifford]

Hi - I have the following code that I need to run on many different reports. For each report, the text string will be different. (For this one it's "COHEN" but for another one it might be "SMITH" or "JONES", etc.

Is there a way to make the text string = value from a cell, or maybe an input box, so i don't have to open VB editor and change the name every time?

Columns("F:F").Select
Selection.FormatConditions.Add Type:=xlTextString, String:="COHEN", _
TextOperator:=xlContains
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
Cells.Select
ActiveWorkbook.Worksheets("Main").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Main").Sort.SortFields.Add(Range("F2"), _
xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = RGB(255, _
199, 206)
ActiveWorkbook.Worksheets("Main").Sort.SortFields.Add Key:=Range("B2"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Main").Sort
.SetRange Range("A1:M9999")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

[Norie]

Perhaps, if the name is in A2.

Please Login or Register  to view this content.

PS Can you add code tags when posting code?

[kl_clifford]

Thanks, I was actually able to figure out the issue, but now i'm stuck on this one:

Please Login or Register  to view this content.

the name for each report will be populating in cell N4. Is it possible to search whatever text is in N4 instead of changing the text string over and over?

[skywriter]

If you want N4 in your formula instead of James Cohen, then try this:

Please Login or Register  to view this content.

[kl_clifford]

nope, unfortunately that just returns all "Ecard" in the destination cells