Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have set up multiple tabs on excel 2013, I have created a conditional format to highlight box red when duplicate information is entered. However I would like this also to happen on the different tabs.

When I copy the format onto the different work sheets, duplicate information is highlighting red but only on the same sheet. I want it to show if there are duplicates from any other work sheets also.

I've tried using the format painter for the different sheets, though this just copies the format to only duplicate for what is on each sheet instead of all the sheets.

Is there any way of doing this?

share|improve this question
    
What have you tried or researched so far? –  CharlieRB Apr 29 at 11:51
    
ive tried using the format painter for the different sheets, though this just copy the format to only duplicate for what is on each sheet instead of all the sheets –  Emma Apr 29 at 11:54

1 Answer 1

To apply conditional formatting across multiple sheets within a workbook will take some work. There is no "point & click" solution that I have been able to find. Here are a couple solutions to help you find your way. The bottom line; you will have to do some experimenting to make this work for your specific application.


Using Defined Names - Excel Magic Trick 879: Conditional Formatting Across Worksheet (Sheets or Tabs)

See how to create Conditional Formatting when Criteria is on one sheet and the Conditional Formatting must be applied on another sheet. Learn about Defined Names. See how to format an intersecting Value. See a Logical Formula that uses the AND and LOOKUP functions.


Using VBA - You can use a VBA macro to look through the sheets and highlight (format) duplicates found. (source)

Sub Highlight_Duplicate()

Dim loop_sheet As Integer
Dim sheet_usedrow As Long
Dim loop_row As Long
Dim compare_value As String
Dim loop_row_first_sheet As Long
Dim first_row_count As Long
Dim column_number As Integer

column_number = 1 ' Change the column_number as the column which you want to highlight.

first_row_count = Sheets(1).UsedRange.Rows.Count

For loop_row_first_sheet = 2 To first_row_count

compare_value = Sheets(1).Cells(loop_row_first_sheet, column_number)

    For loop_sheet = 2 To 6

        sheet_usedrow = Sheets(loop_sheet).UsedRange.Rows.Count

        For loop_row = 2 To sheet_usedrow

            If Sheets(loop_sheet).Cells(loop_row, column_number) = compare_value Then

                Sheets(loop_sheet).Activate

                ActiveSheet.Cells(loop_row, column_number).Select

                With Selection.Interior

                    .Pattern = xlSolid

                    .PatternColorIndex = xlAutomatic

                    .Color = 255

                    .TintAndShade = 0

                    .PatternTintAndShade = 0

                End With

            End If

        Next

    Next

    Sheets(1).Activate

    ActiveSheet.Cells(loop_row_first_sheet, column_number).Select

    With Selection.Interior

        .Pattern = xlSolid

        .PatternColorIndex = xlAutomatic

        .Color = 255

        .TintAndShade = 0

        .PatternTintAndShade = 0

    End With

Next

End Sub
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.