Results 1 to 2 of 2

Thread: Comparing color indexes in two different sheets?
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Sep 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Comparing color indexes in two different sheets?

    Hello,
    I recently got introduced to VBA and I will say I am completely new to programming.

    I have 3 sheets
    Sheet1 - Contains Current weeks data, I just call it "Curr"
    Sheet2 - Contains Previous weeks data, I call it "Prev"
    Sheet3 - Contains the results, I call it "Result" where I compare the data in sheet1 and 2.

    What I'm trying to do is compare the color indexes of two sheets and if the color index of the table in sheet2 is greater than the color index of the table in sheet1, then it will return arrow up (Unicode 8593), if color is the same it returns arrow right (Unicode 8594) and lastly if color index is lower in sheet1 than sheet2 it will return arrow down (Unicode 8595).

    Also the color on the Sheet3 have to match the color on Sheet1

    Code:
    Sub test()
    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim Result As Worksheet
    Dim Curr As Integer
    Dim Prev As Integer
    Dim CurrData As Range
    Dim PrevData As Range
    Dim Cell As Range
    Dim AU As String
    Dim AR As String
    Dim AD As String
    Dim RR As Range
    AU = ChrW(8593)
    AR = ChrW(8594)
    AD = ChrW(8595)
    Curr = 1
    Prev = 2
    Set RR = wb.Sheets("Resultater 2020").Range("E7:G14")
    Set Result = wb.Sheets("Resultater 2020")
    Set CurrData = wb.Sheets(1).Range("E21:E28, H21:I28")
    Set PrevData = wb.Sheets(2).Range("E21:E28, H21:I28")
            For Each Cell In CurrData
                If Cell.Interior.ColorIndex = 15 Then
                    RR.Value = "Done"
                    RR.Font.Color = rbBlack
                ElseIf Cell.Interior.ColorIndex = 1 Then
                    RR.Value = "Ingen rap."
                    RR.Font.Color = rbWhite
                ElseIf Cell.Interior.ColorIndex > PrevData.ColorIndex Then
                    RR.Value = AU
                    RR.Font.Color = rbBlack
                ElseIf Cell.Interior.ColorIndex = PrevData.ColorIndex Then
                    RR.Value = AR
                    RR.Font.Color = rbBlack
                ElseIf Cell.Interior.ColorIndex < PrevData.ColorIndex Then
                    RR.Value = AD
                    RR.Font.Color = rbBlack
                End If
            Next
    End Sub
    Previous version (long and very ineffective:
    [SPOILER]
    Code:
    Sub Test2()
    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim Result As Worksheet
    Dim Curr As Integer
    Dim Prev As Integer
    Curr = 1
    Prev = 2
    Set Result = wb.Sheets("Resultater 2020")
    'Match colors
        'Udvikling gennem samarbejde
            'Skibsbygning 2150
            Result.Range("E7").Interior.Color = Worksheets(Curr).Range("E21").Interior.Color
            Result.Range("F7").Interior.Color = Worksheets(Curr).Range("H21").Interior.Color
            Result.Range("G7").Interior.Color = Worksheets(Curr).Range("I21").Interior.Color
                If Worksheets(Curr).Range("E21").Interior.ColorIndex = 15 Then
                    Result.Range("E7").Value = "Done"
                    ElseIf Worksheets(Curr).Range("E21").Interior.ColorIndex = 1 Then
                    Result.Range("E7").Value = "Ingen rap."
                    Result.Range("E7").Font.Color = vbWhite
                    ElseIf Worksheets(Prev).Range("E21").Interior.ColorIndex < Worksheets(Curr).Range("E21").Interior.ColorIndex Then
                    Result.Range("E7").Value = Result.Range("C18").Value
                    ElseIf Worksheets(Prev).Range("E21").Interior.ColorIndex = Worksheets(Curr).Range("E21").Interior.ColorIndex Then
                    Result.Range("E7").Value = Result.Range("C19").Value
                    ElseIf Worksheets(Prev).Range("E21").Interior.ColorIndex > Worksheets(Curr).Range("E21").Interior.ColorIndex Then
                    Result.Range("E7").Value = Result.Range("C20").Value
                End If
                If Worksheets(Curr).Range("H21").Interior.ColorIndex = 15 Then
                    Result.Range("F7").Value = "Done"
                    ElseIf Worksheets(Curr).Range("H21").Interior.ColorIndex = 1 Then
                    Result.Range("F7").Value = "Ingen rap."
                    ElseIf Worksheets(Prev).Range("H21").Interior.ColorIndex < Worksheets(Curr).Range("H21").Interior.ColorIndex Then
                    Result.Range("F7").Value = Result.Range("C18").Value
                    ElseIf Worksheets(Prev).Range("H21").Interior.ColorIndex = Worksheets(Curr).Range("H21").Interior.ColorIndex Then
                    Result.Range("F7").Value = Result.Range("C19").Value
                    ElseIf Worksheets(Prev).Range("H21").Interior.ColorIndex > Worksheets(Curr).Range("H21").Interior.ColorIndex Then
                    Result.Range("F7").Value = Result.Range("C20").Value
                End If
                If Worksheets(Curr).Range("I21").Interior.ColorIndex = 15 Then
                    Result.Range("G7").Value = "Done"
                    ElseIf Worksheets(Curr).Range("I21").Interior.ColorIndex = 1 Then
                    Result.Range("G7").Value = "Ingen rap."
                    ElseIf Worksheets(Prev).Range("I21").Interior.ColorIndex < Worksheets(Curr).Range("I21").Interior.ColorIndex Then
                    Result.Range("G7").Value = Result.Range("C18").Value
                    ElseIf Worksheets(Prev).Range("I21").Interior.ColorIndex = Worksheets(Curr).Range("I21").Interior.ColorIndex Then
                    Result.Range("G7").Value = Result.Range("C19").Value
                    ElseIf Worksheets(Prev).Range("I21").Interior.ColorIndex > Worksheets(Curr).Range("I21").Interior.ColorIndex Then
                    Result.Range("G7").Value = Result.Range("C20").Value
                End If
            'VSA Implementering
            Result.Range("E8").Interior.Color = Worksheets(Curr).Range("E22").Interior.Color
            Result.Range("F8").Interior.Color = Worksheets(Curr).Range("H22").Interior.Color
            Result.Range("G8").Interior.Color = Worksheets(Curr).Range("I22").Interior.Color
                If Worksheets(Curr).Range("E22").Interior.ColorIndex = 15 Then
                    Result.Range("E8").Value = "Done"
                    ElseIf Worksheets(Curr).Range("E22").Interior.ColorIndex = 1 Then
                    Result.Range("E8").Value = "Ingen rap."
                    ElseIf Worksheets(Prev).Range("E22").Interior.ColorIndex < Worksheets(Curr).Range("E22").Interior.ColorIndex Then
                    Result.Range("E8").Value = Result.Range("C18").Value
                    ElseIf Worksheets(Prev).Range("E22").Interior.ColorIndex = Worksheets(Curr).Range("E22").Interior.ColorIndex Then
                    Result.Range("E8").Value = Result.Range("C19").Value
                    ElseIf Worksheets(Prev).Range("E22").Interior.ColorIndex > Worksheets(Curr).Range("E22").Interior.ColorIndex Then
                    Result.Range("E8").Value = Result.Range("C20").Value
                End If
                If Worksheets(Curr).Range("H22").Interior.ColorIndex = 15 Then
                    Result.Range("F8").Value = "Done"
                    ElseIf Worksheets(Curr).Range("H22").Interior.ColorIndex = 1 Then
                    Result.Range("F8").Value = "Ingen rap."
                    ElseIf Worksheets(Prev).Range("H22").Interior.ColorIndex < Worksheets(Curr).Range("H22").Interior.ColorIndex Then
                    Result.Range("F8").Value = Result.Range("C18").Value
                    ElseIf Worksheets(Prev).Range("H22").Interior.ColorIndex = Worksheets(Curr).Range("H22").Interior.ColorIndex Then
                    Result.Range("F8").Value = Result.Range("C19").Value
                    ElseIf Worksheets(Prev).Range("H22").Interior.ColorIndex > Worksheets(Curr).Range("H22").Interior.ColorIndex Then
                    Result.Range("F8").Value = Result.Range("C20").Value
                End If
                If Worksheets(Curr).Range("I22").Interior.ColorIndex = 15 Then
                    Result.Range("G8").Value = "Done"
                    ElseIf Worksheets(Curr).Range("I22").Interior.ColorIndex = 1 Then
                    Result.Range("G8").Value = "Ingen rap."
                    ElseIf Worksheets(Prev).Range("I22").Interior.ColorIndex < Worksheets(Curr).Range("I22").Interior.ColorIndex Then
                    Result.Range("G8").Value = Result.Range("C18").Value
                    ElseIf Worksheets(Prev).Range("I22").Interior.ColorIndex = Worksheets(Curr).Range("I22").Interior.ColorIndex Then
                    Result.Range("G8").Value = Result.Range("C19").Value
                    ElseIf Worksheets(Prev).Range("I22").Interior.ColorIndex > Worksheets(Curr).Range("I22").Interior.ColorIndex Then
                    Result.Range("G8").Value = Result.Range("C20").Value
                End If
            'Konkurrencemyndighed og andet regulatory og transportdokumenter
            Result.Range("E9").Interior.Color = Worksheets(Curr).Range("E23").Interior.Color
            Result.Range("F9").Interior.Color = Worksheets(Curr).Range("H23").Interior.Color
            Result.Range("G9").Interior.Color = Worksheets(Curr).Range("I23").Interior.Color
                If Worksheets(Curr).Range("E23").Interior.ColorIndex = 15 Then
                    Result.Range("E9").Value = "Done"
                    ElseIf Worksheets(Curr).Range("E23").Interior.ColorIndex = 1 Then
                    Result.Range("E9").Value = "Ingen rap."
                    ElseIf Worksheets(Prev).Range("E23").Interior.ColorIndex < Worksheets(Curr).Range("E23").Interior.ColorIndex Then
                    Result.Range("E9").Value = Result.Range("C18").Value
                    ElseIf Worksheets(Prev).Range("E23").Interior.ColorIndex = Worksheets(Curr).Range("E23").Interior.ColorIndex Then
                    Result.Range("E9").Value = Result.Range("C19").Value
                    ElseIf Worksheets(Prev).Range("E23").Interior.ColorIndex > Worksheets(Curr).Range("E23").Interior.ColorIndex Then
                    Result.Range("E9").Value = Result.Range("C20").Value
                End If
                If Worksheets(Curr).Range("H23").Interior.ColorIndex = 15 Then
                    Result.Range("F9").Value = "Done"
                    ElseIf Worksheets(Curr).Range("H23").Interior.ColorIndex = 1 Then
                    Result.Range("F9").Value = "Ingen rap."
                    ElseIf Worksheets(Prev).Range("H23").Interior.ColorIndex < Worksheets(Curr).Range("H23").Interior.ColorIndex Then
                    Result.Range("F9").Value = Result.Range("C18").Value
                    ElseIf Worksheets(Prev).Range("H23").Interior.ColorIndex = Worksheets(Curr).Range("H23").Interior.ColorIndex Then
                    Result.Range("F9").Value = Result.Range("C19").Value
                    ElseIf Worksheets(Prev).Range("H23").Interior.ColorIndex > Worksheets(Curr).Range("H23").Interior.ColorIndex Then
                    Result.Range("F9").Value = Result.Range("C20").Value
                End If
                If Worksheets(Curr).Range("I23").Interior.ColorIndex = 15 Then
                    Result.Range("G9").Value = "Done"
                    ElseIf Worksheets(Curr).Range("I23").Interior.ColorIndex = 1 Then
                    Result.Range("G9").Value = "Ingen rap."
                    ElseIf Worksheets(Prev).Range("I23").Interior.ColorIndex < Worksheets(Curr).Range("I23").Interior.ColorIndex Then
                    Result.Range("G9").Value = Result.Range("C18").Value
                    ElseIf Worksheets(Prev).Range("I23").Interior.ColorIndex = Worksheets(Curr).Range("I23").Interior.ColorIndex Then
                    Result.Range("G9").Value = Result.Range("C19").Value
                    ElseIf Worksheets(Prev).Range("I23").Interior.ColorIndex > Worksheets(Curr).Range("I23").Interior.ColorIndex Then
                    Result.Range("G9").Value = Result.Range("C20").Value
                End If
            'Forberedelse Aalborg Havn
            Result.Range("E10").Interior.Color = Worksheets(Curr).Range("E24").Interior.Color
            Result.Range("F10").Interior.Color = Worksheets(Curr).Range("H24").Interior.Color
            Result.Range("G10").Interior.Color = Worksheets(Curr).Range("I24").Interior.Color
                If Worksheets(Curr).Range("E24").Interior.ColorIndex = 15 Then
                    Result.Range("E10").Value = "Done"
                    ElseIf Worksheets(Curr).Range("E24").Interior.ColorIndex = 1 Then
                    Result.Range("E10").Value = "Ingen rap."
                    ElseIf Worksheets(Prev).Range("E24").Interior.ColorIndex < Worksheets(Curr).Range("E24").Interior.ColorIndex Then
                    Result.Range("E10").Value = Result.Range("C18").Value
                    ElseIf Worksheets(Prev).Range("E24").Interior.ColorIndex = Worksheets(Curr).Range("E24").Interior.ColorIndex Then
                    Result.Range("E10").Value = Result.Range("C19").Value
                    ElseIf Worksheets(Prev).Range("E24").Interior.ColorIndex > Worksheets(Curr).Range("E24").Interior.ColorIndex Then
                    Result.Range("E10").Value = Result.Range("C20").Value
                End If
                If Worksheets(Curr).Range("H24").Interior.ColorIndex = 15 Then
                    Result.Range("F10").Value = "Done"
                    ElseIf Worksheets(Curr).Range("H24").Interior.ColorIndex = 1 Then
                    Result.Range("F10").Value = "Ingen rap."
                    ElseIf Worksheets(Prev).Range("H24").Interior.ColorIndex < Worksheets(Curr).Range("H24").Interior.ColorIndex Then
                    Result.Range("F10").Value = Result.Range("C18").Value
                    ElseIf Worksheets(Prev).Range("H24").Interior.ColorIndex = Worksheets(Curr).Range("H24").Interior.ColorIndex Then
                    Result.Range("F10").Value = Result.Range("C19").Value
                    ElseIf Worksheets(Prev).Range("H24").Interior.ColorIndex > Worksheets(Curr).Range("H24").Interior.ColorIndex Then
                    Result.Range("F10").Value = Result.Range("C20").Value
                End If
                If Worksheets(Curr).Range("I24").Interior.ColorIndex = 15 Then
                    Result.Range("G10").Value = "Done"
                    ElseIf Worksheets(Curr).Range("I24").Interior.ColorIndex = 1 Then
                    Result.Range("G10").Value = "Ingen rap."
                    ElseIf Worksheets(Prev).Range("I24").Interior.ColorIndex < Worksheets(Curr).Range("I24").Interior.ColorIndex Then
                    Result.Range("G10").Value = Result.Range("C18").Value
                    ElseIf Worksheets(Prev).Range("I24").Interior.ColorIndex = Worksheets(Curr).Range("I24").Interior.ColorIndex Then
                    Result.Range("G10").Value = Result.Range("C19").Value
                    ElseIf Worksheets(Prev).Range("I24").Interior.ColorIndex > Worksheets(Curr).Range("I24").Interior.ColorIndex Then
                    Result.Range("G10").Value = Result.Range("C20").Value
                End If
            'Forberedelse Aarhus havn
            Result.Range("E11").Interior.Color = Worksheets(Curr).Range("E25").Interior.Color
            Result.Range("F11").Interior.Color = Worksheets(Curr).Range("H25").Interior.Color
            Result.Range("G11").Interior.Color = Worksheets(Curr).Range("I25").Interior.Color
                If Worksheets(Curr).Range("E25").Interior.ColorIndex = 15 Then
                    Result.Range("E11").Value = "Done"
                    ElseIf Worksheets(Curr).Range("E25").Interior.ColorIndex = 1 Then
                    Result.Range("E11").Value = "Ingen rap."
                    ElseIf Worksheets(Prev).Range("E25").Interior.ColorIndex < Worksheets(Curr).Range("E25").Interior.ColorIndex Then
                    Result.Range("E11").Value = Result.Range("C18").Value
                    ElseIf Worksheets(Prev).Range("E25").Interior.ColorIndex = Worksheets(Curr).Range("E25").Interior.ColorIndex Then
                    Result.Range("E11").Value = Result.Range("C19").Value
                    ElseIf Worksheets(Prev).Range("E25").Interior.ColorIndex > Worksheets(Curr).Range("E25").Interior.ColorIndex Then
                    Result.Range("E11").Value = Result.Range("C20").Value
                End If
                If Worksheets(Curr).Range("H25").Interior.ColorIndex = 15 Then
                    Result.Range("F11").Value = "Done"
                    ElseIf Worksheets(Curr).Range("H25").Interior.ColorIndex = 1 Then
                    Result.Range("F11").Value = "Ingen rap."
                    ElseIf Worksheets(Prev).Range("H25").Interior.ColorIndex < Worksheets(Curr).Range("H25").Interior.ColorIndex Then
                    Result.Range("F11").Value = Result.Range("C18").Value
                    ElseIf Worksheets(Prev).Range("H25").Interior.ColorIndex = Worksheets(Curr).Range("H25").Interior.ColorIndex Then
                    Result.Range("F11").Value = Result.Range("C19").Value
                    ElseIf Worksheets(Prev).Range("H25").Interior.ColorIndex > Worksheets(Curr).Range("H25").Interior.ColorIndex Then
                    Result.Range("F11").Value = Result.Range("C20").Value
                End If
                If Worksheets(Curr).Range("I25").Interior.ColorIndex = 15 Then
                    Result.Range("G11").Value = "Done"
                    ElseIf Worksheets(Curr).Range("I25").Interior.ColorIndex = 1 Then
                    Result.Range("G11").Value = "Ingen rap."
                    ElseIf Worksheets(Prev).Range("I25").Interior.ColorIndex < Worksheets(Curr).Range("I25").Interior.ColorIndex Then
                    Result.Range("G11").Value = Result.Range("C18").Value
                    ElseIf Worksheets(Prev).Range("I25").Interior.ColorIndex = Worksheets(Curr).Range("I25").Interior.ColorIndex Then
                    Result.Range("G11").Value = Result.Range("C19").Value
                    ElseIf Worksheets(Prev).Range("I25").Interior.ColorIndex > Worksheets(Curr).Range("I25").Interior.ColorIndex Then
                    Result.Range("G11").Value = Result.Range("C20").Value
                End If
            'Forberedelse Reykjavik havn
            Result.Range("E12").Interior.Color = Worksheets(Curr).Range("E26").Interior.Color
            Result.Range("F12").Interior.Color = Worksheets(Curr).Range("H26").Interior.Color
            Result.Range("G12").Interior.Color = Worksheets(Curr).Range("I26").Interior.Color
                If Worksheets(Curr).Range("I26").Interior.ColorIndex = 15 Then
                    Result.Range("E12").Value = "Done"
                    ElseIf Worksheets(Curr).Range("I26").Interior.ColorIndex = 1 Then
                    Result.Range("E12").Value = "Ingen rap."
                    ElseIf Worksheets(Prev).Range("E26").Interior.ColorIndex < Worksheets(Curr).Range("E26").Interior.ColorIndex Then
                    Result.Range("E12").Value = Result.Range("C18").Value
                    ElseIf Worksheets(Prev).Range("E26").Interior.ColorIndex = Worksheets(Curr).Range("E26").Interior.ColorIndex Then
                    Result.Range("E12").Value = Result.Range("C19").Value
                    ElseIf Worksheets(Prev).Range("E26").Interior.ColorIndex > Worksheets(Curr).Range("E26").Interior.ColorIndex Then
                    Result.Range("E12").Value = Result.Range("C20").Value
                End If
                If Worksheets(Curr).Range("H26").Interior.ColorIndex = 15 Then
                    Result.Range("F12").Value = "Done"
                    ElseIf Worksheets(Curr).Range("H26").Interior.ColorIndex = 1 Then
                    Result.Range("F12").Value = "Ingen rap."
                    ElseIf Worksheets(Prev).Range("H26").Interior.ColorIndex < Worksheets(Curr).Range("H26").Interior.ColorIndex Then
                    Result.Range("F12").Value = Result.Range("C18").Value
                    ElseIf Worksheets(Prev).Range("H26").Interior.ColorIndex = Worksheets(Curr).Range("H26").Interior.ColorIndex Then
                    Result.Range("F12").Value = Result.Range("C19").Value
                    ElseIf Worksheets(Prev).Range("H26").Interior.ColorIndex > Worksheets(Curr).Range("H26").Interior.ColorIndex Then
                    Result.Range("F12").Value = Result.Range("C20").Value
                End If
                If Worksheets(Curr).Range("I26").Interior.ColorIndex = 15 Then
                    Result.Range("G12").Value = "Done"
                    ElseIf Worksheets(Curr).Range("I26").Interior.ColorIndex = 1 Then
                    Result.Range("G12").Value = "Ingen rap."
                    ElseIf Worksheets(Prev).Range("I26").Interior.ColorIndex < Worksheets(Curr).Range("I26").Interior.ColorIndex Then
                    Result.Range("G12").Value = Result.Range("C18").Value
                    ElseIf Worksheets(Prev).Range("I26").Interior.ColorIndex = Worksheets(Curr).Range("I26").Interior.ColorIndex Then
                    Result.Range("G12").Value = Result.Range("C19").Value
                    ElseIf Worksheets(Prev).Range("I26").Interior.ColorIndex > Worksheets(Curr).Range("I26").Interior.ColorIndex Then
                    Result.Range("G12").Value = Result.Range("C20").Value
                End If
        'Fundament Styrkes
            'Nye Bygdeskibe
            Result.Range("E13").Interior.Color = Worksheets(Curr).Range("E27").Interior.Color
            Result.Range("F13").Interior.Color = Worksheets(Curr).Range("H27").Interior.Color
            Result.Range("G13").Interior.Color = Worksheets(Curr).Range("I27").Interior.Color
                If Worksheets(Curr).Range("E27").Interior.ColorIndex = 15 Then
                    Result.Range("E13").Value = "Done"
                    ElseIf Worksheets(Curr).Range("E27").Interior.ColorIndex = 1 Then
                    Result.Range("E13").Value = "Ingen rap."
                    ElseIf Worksheets(Prev).Range("E27").Interior.ColorIndex < Worksheets(Curr).Range("E27").Interior.ColorIndex Then
                    Result.Range("E13").Value = Result.Range("C18").Value
                    ElseIf Worksheets(Prev).Range("E27").Interior.ColorIndex = Worksheets(Curr).Range("E27").Interior.ColorIndex Then
                    Result.Range("E13").Value = Result.Range("C19").Value
                    ElseIf Worksheets(Prev).Range("E27").Interior.ColorIndex > Worksheets(Curr).Range("E27").Interior.ColorIndex Then
                    Result.Range("E13").Value = Result.Range("C20").Value
                End If
                If Worksheets(Curr).Range("H27").Interior.ColorIndex = 15 Then
                    Result.Range("F13").Value = "Done"
                    ElseIf Worksheets(Curr).Range("H27").Interior.ColorIndex = 1 Then
                    Result.Range("F13").Value = "Ingen rap."
                    ElseIf Worksheets(Prev).Range("H27").Interior.ColorIndex < Worksheets(Curr).Range("H27").Interior.ColorIndex Then
                    Result.Range("F13").Value = Result.Range("C18").Value
                    ElseIf Worksheets(Prev).Range("H27").Interior.ColorIndex = Worksheets(Curr).Range("H27").Interior.ColorIndex Then
                    Result.Range("F13").Value = Result.Range("C19").Value
                    ElseIf Worksheets(Prev).Range("H27").Interior.ColorIndex > Worksheets(Curr).Range("H27").Interior.ColorIndex Then
                    Result.Range("F13").Value = Result.Range("C20").Value
                End If
                If Worksheets(Curr).Range("I27").Interior.ColorIndex = 15 Then
                    Result.Range("G13").Value = "Done"
                    ElseIf Worksheets(Curr).Range("I27").Interior.ColorIndex = 1 Then
                    Result.Range("G13").Value = "Ingen rap."
                    ElseIf Worksheets(Prev).Range("I27").Interior.ColorIndex < Worksheets(Curr).Range("I27").Interior.ColorIndex Then
                    Result.Range("G13").Value = Result.Range("C18").Value
                    ElseIf Worksheets(Prev).Range("I27").Interior.ColorIndex = Worksheets(Curr).Range("I27").Interior.ColorIndex Then
                    Result.Range("G13").Value = Result.Range("C19").Value
                    ElseIf Worksheets(Prev).Range("I27").Interior.ColorIndex > Worksheets(Curr).Range("I27").Interior.ColorIndex Then
                    Result.Range("G13").Value = Result.Range("C20").Value
                End If
        'Kommunikation
            'Forberedelse af Stakeholders
            Result.Range("E14").Interior.Color = Worksheets(Curr).Range("E28").Interior.Color
            Result.Range("F14").Interior.Color = Worksheets(Curr).Range("H28").Interior.Color
            Result.Range("G14").Interior.Color = Worksheets(Curr).Range("I28").Interior.Color
                If Worksheets(Curr).Range("E28").Interior.ColorIndex = 15 Then
                    Result.Range("E14").Value = "Done"
                    ElseIf Worksheets(Curr).Range("E28").Interior.ColorIndex = 1 Then
                    Result.Range("E14").Value = "Ingen rap."
                    ElseIf Worksheets(Prev).Range("E28").Interior.ColorIndex < Worksheets(Curr).Range("E28").Interior.ColorIndex Then
                    Result.Range("E14").Value = Result.Range("C18").Value
                    ElseIf Worksheets(Prev).Range("E28").Interior.ColorIndex = Worksheets(Curr).Range("E28").Interior.ColorIndex Then
                    Result.Range("E14").Value = Result.Range("C19").Value
                    ElseIf Worksheets(Prev).Range("E28").Interior.ColorIndex > Worksheets(Curr).Range("E28").Interior.ColorIndex Then
                    Result.Range("E14").Value = Result.Range("C20").Value
                End If
                If Worksheets(Curr).Range("H28").Interior.ColorIndex = 15 Then
                    Result.Range("F14").Value = "Done"
                    ElseIf Worksheets(Curr).Range("H28").Interior.ColorIndex = 1 Then
                    Result.Range("F14").Value = "Ingen rap."
                    ElseIf Worksheets(Prev).Range("H28").Interior.ColorIndex < Worksheets(Curr).Range("H28").Interior.ColorIndex Then
                    Result.Range("F14").Value = Result.Range("C18").Value
                    ElseIf Worksheets(Prev).Range("H28").Interior.ColorIndex = Worksheets(Curr).Range("H28").Interior.ColorIndex Then
                    Result.Range("F14").Value = Result.Range("C19").Value
                    ElseIf Worksheets(Prev).Range("H28").Interior.ColorIndex > Worksheets(Curr).Range("H28").Interior.ColorIndex Then
                    Result.Range("F14").Value = Result.Range("C20").Value
                End If
                If Worksheets(Curr).Range("I28").Interior.ColorIndex = 15 Then
                    Result.Range("G14").Value = "Done"
                    ElseIf Worksheets(Curr).Range("I28").Interior.ColorIndex = 1 Then
                    Result.Range("G14").Value = "Ingen rap."
                    ElseIf Worksheets(Prev).Range("I28").Interior.ColorIndex < Worksheets(Curr).Range("I28").Interior.ColorIndex Then
                    Result.Range("G14").Value = Result.Range("C18").Value
                    ElseIf Worksheets(Prev).Range("I28").Interior.ColorIndex = Worksheets(Curr).Range("I28").Interior.ColorIndex Then
                    Result.Range("G14").Value = Result.Range("C19").Value
                    ElseIf Worksheets(Prev).Range("I28").Interior.ColorIndex > Worksheets(Curr).Range("I28").Interior.ColorIndex Then
                    Result.Range("G14").Value = Result.Range("C20").Value
                End If
    End Sub
    [/SPOILER]

  2. #2
    New Member
    Join Date
    Sep 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Comparing color indexes in two different sheets?

    Sorry let me clear up some things, thought my post had updated before I posted it.

    I have 3 sheets
    Sheet1: Current weeks data (named Curr)
    Sheet2: Previous weeks data (named Prev)
    Sheet3: Results (named Result)

    I want to compare the color indexes of Sheet1 and Sheet2 of the cells in E21:E28 and H21:I28 and post the results in Sheet3 E7:G14

    For the values I have 5 conditions as well.
    Condition 1: If color index in sheet1 is 15 (gray) then it will say "Done" in the respective cell in Sheet3.
    Condition 2: If color index in sheet1 is 1 (black) then it will say "No rap." in respective cell in Sheet3.
    Condition 3: I want to compare the cells of the table in sheet1 with the cells of the table in sheet2, if the color indexes are greater than the ones in sheet2, result is arrow up.
    Condition 4: Similar to condition 3, but if the color indexes are equal to one another the arrow will be arrow to the right.
    Condition 5: Similar to Condition 3, but where the color indexes are lower than the cells of the table in sheet2 and will return arrow down.

    For the arrows I use unicode, though in the larger code I made earlier I referenced C18 (Arrow up), C19 (Arrow Right) and C20 (Arrow Down).

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •