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]

To return a cell's number format, try like this

NFmt=cell.numberformat

 

[Legacy 93538]

Hi

I had a go at creating a loop if statement which should look at the cell formatting and determine what whether its a number or a percentage but it doesn't it just returns an error saying "else without if"

For Each cell In Sheets("PPIIIFORM").Range("F4:U644")
    If cell.NumberFormat = "%" Then Sheets("Input_Reference_Table").Cells(Nrow, 8).Value = "%10.0%%"
    Else: If cell.NumberFormat = "o" Then Sheets("Input_Reference_Table").Cells(Nrow, 8).Value = "%10.0%"
    Else: Sheets("Input_Reference_Table").Cells(Nrow, 8).Value = "false"
    Nrow = Nrow + 1
    Application.StatusBar = cell.Address
Next cell

 

[VoG]

Perhaps like this

For Each cell In Sheets("PPIIIFORM").Range("F4:U644")
    If Right(cell.NumberFormat, 1) = "%" Then
        Sheets("Input_Reference_Table").Cells(Nrow, 8).Value = "%10.0%%"
    ElseIf Right(cell.NumberFormat, 1) = "0" Then
        Sheets("Input_Reference_Table").Cells(Nrow, 8).Value = "%10.0%"
    Else
        Sheets("Input_Reference_Table").Cells(Nrow, 8).Value = False
    End If
    Nrow = Nrow + 1
    Application.StatusBar = cell.Address
Next cell

 

[Legacy 93538]

Hi

Working now but hwot to say if its a percentage and has a decimal place and then if it does have the value "%10.(whatever the percentage is)%%"

 

[VoG]

See if this helps - you might want to code something like this as a subroutine to save having oodles of code within your lop

Sub test()
Dim Nfmt As String, NDec As Long
Nfmt = "0.000%"
If Nfmt Like "*.*" Then ' percentage with decimals
    NDec = Len(Nfmt) - InStr(Nfmt, ".") - 1
    MsgBox "Format is " & NDec & " decimals"
Else
    MsgBox "No decimals"
End If
End Sub

 

[VoG]

Like this:

For Each cell In Sheets("PPIIIFORM").Range("F4:U644")
    Sheets("Input_Reference_Table").Cells(Nrow, 8).Value = ParseNFmt(cell.NumberFormat)
    Nrow = Nrow + 1
    Application.StatusBar = cell.Address
Next cell

then after End Sub

Function ParseNFmt(NFmt As String) As String
Dim NDec As Long, NType As String
NType = IIf(Right(NFmt, 1) = "%", "Percent", "Number")
If NFmt Like "*.*" Then ' has decimals
    NDec = Len(NFmt) - InStr(NFmt, ".")
    If NType = "Percent" Then NDec = NDec - 1
Else
    NDec = 0
End If
ParseNFmt = NType & " " & NDec & " decimals"
End Function

Incidentally, why are you checking columns F to U but returning all the results to column H of the other sheet?

 

[Legacy 93538]

Hi

I have it working now thanks it works perfectly!!!

Thank you so much for all your help its greatly appricated!!!