Comparing color indexes in two different sheets?

N

NiklasR

New Member
Joined
Sep 4, 2019
Messages
2
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:
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
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Sort by date Sort by votes
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).
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,224,827
Messages
6,181,194
Members
453,021
Latest member
pingpong7117

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top