2

The current formula I'm using is as follows:

Columns("D:D").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
    "=$D1='General Profiling'!$B$6"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .Color = 65535
    .TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = True

However at times, there may be values in C6, D6, etc which I also wish for the conditional formatting to pick up and highlight.

Is there a way to identify whether a value has been put in between C2 to C100 and then highlight these values on a different spreadsheet?

kitarika
  • 175
  • 1
  • 12
  • Should be: Is there a way to identify whether a value has been put in between B6 to Z6 and then highlight these values on a different spreadsheet? – kitarika Jun 15 '16 at 04:34

1 Answers1

1

Try to avoid using Range .Select and Range .Activate methods¹.

With ActiveSheet
    With .Range(.Cells(1, "D"), .Cells(1, Columns.Count).End(xlToLeft)).EntireColumn
        With .FormatConditions.Add(Type:=xlExpression, Formula1:= _
                            "=COUNTIF('General Profiling'!$B$6:$Z6, $D1)")
            .Interior.Color = vbYellow
        End With
    End With
End With

This would be even better if you could change that ActiveSheet property to the name or codename of the Worksheet Object.

.¹ See How to avoid using Select in Excel VBA macros for more methods on getting away from relying on select and activate to accomplish your goals.

Community
  • 1
  • 1
  • @ Jeeped: That worked perfectly! Thank you so much!! – kitarika Jun 15 '16 at 08:32
  • Sorry to bother you again, it seems a common thing with conditional formatting that where the value of the comparison cell is empty, the formula highlights all rows of the empty cell. Is there a way to exclude empty cells in this instance? – kitarika Jun 17 '16 at 07:54