VBA checking colour macro

[Legacy 93538]

Hi

Does anyone know how to write an if statement which checks the colour of a cell range?

I ahve a loop which loops through a cell range but its not checking the right colour. I know RGA codes for the colour i want it to check but i am not sure how to use RGA in this loop.

    If Selection.Interior.Color = 65535 Then
        Sheets("Input_Reference_Table").Cells(Nrow, 14).Value = "True"
    Else: Sheets("Input_Reference_Table").Cells(Nrow, 14).Value = "False"
    End If

Can anyone help me?

Thanks

Jessicaseymour

 

[VoG]

It would be like this (substitute the correct RGB colour

Sheets("Input_Reference_Table").Cells(Nrow, 14).Value = Selection.Interior.Color = RGB(255, 255, 0)

 

[JamesW]

Hi,

Try this:

    lRow = Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To lRow
        If Cells(i, 1).Interior.ColorIndex = 10 Then
            Sheets("Input_Reference_Table").Cells(Nrow, 14).Value = "True"
        Else
            Sheets("Input_Reference_Table").Cells(Nrow, 14).Value = "False"
        End If
    Next

 

[Legacy 93538]

Hi

Thanks! but its still not checking as its not returning false when the statement says if the cell is this colour place false in the cell but its not have i wrirtten it wrong? This is full loop which consists of 3 if statements the last two are the colour if statements which are not returning the correct results.

For Each cell In Sheets("PPIIIFORM").Range("F4:U644")
    If cell.Value <> "" Then
        Sheets("Input_Reference_Table").Cells(Nrow, 1).Value = cell.Value
    End If
    If Selection.Interior.ColorIndex = RGB(217, 217, 217) Then
        Sheets("Input_Reference_Table").Cells(Nrow, 12).Value = "False"
    Else: Sheets("Input_Reference_Table").Cells(Nrow, 12).Value = "True"
    End If
    If Selection.Interior.ColorIndex = RGB(255, 255, 0) Then
        Sheets("Input_Reference_Table").Cells(Nrow, 14).Value = "True"
    Else: Sheets("Input_Reference_Table").Cells(Nrow, 14).Value = "False"
    End If
    Nrow = Nrow + 1
    Application.StatusBar = cell
Next cell

 

[JamesW]

Hi,

Try changing Selection to cell.

 

[VoG]

Try

For Each cell In Sheets("PPIIIFORM").Range("F4:U644")
    If cell.Value <> "" Then Sheets("Input_Reference_Table").Cells(Nrow, 1).Value = cell.Value
    Sheets("Input_Reference_Table").Cells(Nrow, 12).Value = Not cell.Interior.Color = RGB(217, 217, 217)
    Sheets("Input_Reference_Table").Cells(Nrow, 14).Value = cell.Interior.Color = RGB(255, 255, 0)
    Nrow = Nrow + 1
    Application.StatusBar = cell.Address
Next cell

 

[Legacy 93538]

Hi

Ok i tried changing "Selection" to "Cell" but still did not worked.

VoG - ok but i need to include the two else statements to say if the colour is not put cell in Input_Reference_Table to True for first one and False ofr the second one

 

[VoG]

VoG - ok but i need to include the two else statements to say if the colour is not put cell in Input_Reference_Table to True for first one and False ofr the second one

Did you try it? You don't need an If/Else structure for that type of comparison where you need to return True or False. The expressions that I wrote evaluate to True or False.

 

[Legacy 93538]

Tried works perfectly thanks so much for help!! Sorry when i read i got a little confused sorry!

Thanks everyone for all your help!!! Its greatly appricated

 

[Legacy 93538]

Hi

Sorry about this but would anyone know how to create an if statement which checks the number formatting of a cell?

AS i need to check a cell and:

if it is a percentage with no decimal in Sheets("Input_Reference_Table").Cells value should be %10.0%%

if it is a percentage with a decimal in Sheets("Input_Reference_Table").Cells value should be %10.(whatever the decmial is)%%

if it is a number with no decimal in Sheets("Input_Reference_Table").Cells value should be %10.0%

if it is a number with a decimal in Sheets("Input_Reference_Table").Cells value should be %10.(whatever the decmial is)%

can anyone help?