Get the cell color and replace with value

[krr123]

Hi,


I am new to excel VBA, and here is my requirement.


cell A1 B1

A1 has string(text) wtih background color grey or white


in cell B1 i have to get values like this: if A1's background color is grey then 'True' else 'False'


How can i do this, is this possible with any of excel functions without VBA. if i have to do with vba , can you please help me with sample code.


Thanks,

RK

 

[Luke M]

It's not possible w/o VBA...unless you flip it. Can we make it so that you enter True/False in col B, and this controls which cells get colored in col A? If so, we can just do some conditional formatting.


VB code for the User defined function (UDF): Right click on sheet tab, view code. Insert, - Module. Paste this in:

[pre]

Function ColorCheck(r As Range) As Boolean
ColorCheck = Not (r.Interior.ColorIndex = 2 Or _
r.Interior.ColorIndex = -4142)
End Function

[/pre]
Close the editor.

In your workbook, you can now type this formula:

=ColorCheck(A2)

Function will return TRUE if A2 is not filled white or blank. (I wasn't sure which exact grey you were using)


Note that formula will not auto update if you format the cell, you'll need to force a recalc with F9.

 

[krr123]

Thanks Luke. Your Function helped me in creating a Macro.

 

[Luke M]

Hurray, glad I was able to help.

 

[krr123]

I am using record macro to create a macro. on the first row i am getting the correct value , but on the second row ,getting #VALUE! error. How to auto populate formula to next cells.

can you please take a look at the below code.


Range("J2").Select

ActiveCell.FormulaR1C1 = "FLAG_MONDAY"

Range("J3").Select

ActiveCell.FormulaR1C1 = "=ColorCheck(RC[-7])"

Range("J3").Select

Selection.AutoFill Destination:=Range("J3:J45"), Type:=xlFillDefault


Thanks for all your help.

 

[Luke M]

Rather than putting in formula, and then using AutoFill, we'll just do all the formulas at the same time.

[pre]

Range("J2").FormulaR1C1 = "FLAG_MONDAY"
Range("J3:J45").FormulaR1C1 = "=ColorCheck(RC[-7])"

[/pre]

 

[krr123]

Thanks Luke.I Appreciate your help. I have one more question. I need to get the color index for Grey colors, these colors are first column on the Fill color. If cell color is in these colors , i need show as True else False.

 

[Luke M]

To check if it's from 1st column of color themes, we'll change function to this:

[pre]

Function ColorCheck(r As Range) As Boolean
ColorCheck = (r.Interior.ThemeColor = 1)
End Function

[/pre]

 

[krr123]

Thanks for your Help. I have just received spreadsheet report with the actual color of grey, it's RGB is 192,192,192. For this color it is giving as False. I just need to consider this as grey and show as TRUE.


Can you please help me in modifying this code.


Thanks,

RK

 

[krr123]

Thanks Luke, It's fixed. I am Not sure how to use rgb in this function, so using some other RGB function.


Function getRGB1(rcell) As String

Dim sColor As String

getRGB1 = (rcell.Interior.Color = 12632256)

End Function


Thanks a lot for your help.

 

[krr123]

I have one more question, how to save this spreadsheet as csv on the users desktop.


I am using this code , need replace 1234 with users employee id.


ChDir "C:Users1234Desktop"

ActiveWorkbook.SaveAs Filename:="C:Users123Desktopbook1.csv", _

FileFormat:=xlCSV, CreateBackup:=False


How can i automate this?

Thanks for your help.