VBA if function based on colors

[lcaindoy]

Hope you could help me.

I saw the below code in the internet, however, I don't know why it's not working for me.

Function CheckColor1(range)
If range.Interior.Color = RGB(256, 256, 0) Then
CheckColor1 = "***"
Else
CheckColor1 = ""
End If
End Function

Inputs:
1. I have set of data and one column (Let's say J column) has blank, yellow, and other colors in a set of range (J7 to J500)
2. I have tried the above formula but its not working.

Output:
1. Based on the color on each cell, I want to make a markings.
If yellow, it will note "***"
If other, it will be blank
2. output is on column K

Please let me know if you need further info.

 

[jproffer]

Looks like it's doing what you want. Yellow = "**" otherwise no marking. Is that right?

(this is assuming RGB-256,256,0 is yellow), otherwise just try color 65535...

range.Interior.Color = 65535

 

[lcaindoy]

Looks like it's doing what you want. Yellow = "**" otherwise no marking. Is that right?

(this is assuming RGB-256,256,0 is yellow), otherwise just try color 65535...

range.Interior.Color = 65535

Hi JProffer,

No it's not working, I'm not so familiar with VBA.
What I try is after the above coding, On the cell where I want the output I used the =CheckCode1(Cell) but the output od this is #NAME ?. If I'm right when I type a formula shouldn't it show the checkcolor function as if it is sum function(just for example coz I'm not really sure how the function code in vba works)?

 

[jproffer]

#NAME means your function isn't being found. Where is the function located, and where is the worksheet you're trying to use it on?

 

[lcaindoy]

#NAME means your function isn't being found. Where is the function located, and where is the worksheet you're trying to use it on?

sorry, what do you mean by where is the function located? the worksheet that I'M trying is on the same file and tab.

 

[jproffer]

Is the function in the same workbook as the worksheet where you're using it? In a standard code module?

 

[lcaindoy]

Is the function in the same workbook as the worksheet where you're using it? In a standard code module?

yup same workbook

 

[jproffer]

If it's in the same workbook, it looks to me like it should work. The only change you might try is this:

Function CheckColor1(range as Range)
    If range.Interior.Color = RGB(256, 256, 0) Then
        CheckColor1 = "***"
    Else
        CheckColor1 = ""
    End If
End Function

(dimensioning the range variable passed to the function) Other than that, I can't imagine why it wouldn't work.

 

[lcaindoy]

If it's in the same workbook, it looks to me like it should work. The only change you might try is this:

Function CheckColor1(range as Range)
    If range.Interior.Color = RGB(256, 256, 0) Then
        CheckColor1 = "***"
    Else
        CheckColor1 = ""
    End If
End Function

(dimensioning the range variable passed to the function) Other than that, I can't imagine why it wouldn't work.

Do I need to change something in this code like the range?

 

[jproffer]

To me, it looks like it should work as it is. You would use it like:

=CheckColor1(A1)

and if A1 is yellow (again, assuming 256, 256, 0 is yellow) it should put "***" in the cell the formula is in, which I would assume would be B1.