Excel: Advanced If statements 4

[jds422354]

Please help me i am completely baffled. I need a line of code that helps me differentiate between cell background colours. I have an Excel workbook that has been passed to me. It has coloured cells that mean different things. I am unable to define a statement that can tell the difference between them.

FOR EXAMPLE: if a cell background colour is grey i want a returned value of 1.

Any ideas??

many thanks

James

 

[joerd]

You have to write a visual basic function such as:

Function BackgroundColor(R As Range) As Variant
    BackgroundColor = R.Interior.ColorIndex
End Function

Regards,

Joerd

 

[jds422354]

to joerd, thank you for the swift reply. Unfortunatly i am not very familular with the vba in excel, i am still learning it. below is the macro i have recorded please could you help me implement your solution.

Sub test1()
Range("B6&quot.Select
ActiveCell.FormulaR1C1 = "=IF(sheet2!RC=""cellbackground colour 15 "",""x"",0)"
Range("B6:H6&quot.Select
Selection.FillRight
Range("B6:H19&quot.Select
Selection.FillDown
End Sub

colored text
kind regrads james

 

[xlbo]

What joerd provided was a User Defined Function
This is written in VBA but can be USED on a worksheet

Function ColVal(rng As Range) As Integer
Select Case rng.Interior.ColorIndex
Case 15 'light grey
ColVal = 1
Case 3 'red
ColVal = 2
Case 6 'yellow
ColVal = 3
Case 41 'royal blue
ColVal = 4
Case Else
ColVal = 0
End Select
End Function

You would go to the VB Editor and insert a module (if you do not already have one in the workkbook)

paste this directly into there and then, in the worksheet, use:
=ColVal(A1)
where A1 is the cell you want to test the background colour of

To get the list of the colorindexes, you can use Joerd's function on different coloured cells and it will return the colorindex of that colour. You can then change the cases to be relevant to the colours you wish to test

Hope this is clear Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur