-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
If statement based on cell color?
- Thread starter Alagan
- Start date
Mike Blackman
Well-known Member
- Joined
- Jun 8, 2007
- Messages
- 2,494
Hi Alagan,
I have purely used this to check on cells E19:E20 but is easily altered;
the for i is the range of rows you are checking with 19 To 20 being row numbers.
Cells(i , 6) equates to Cells(Row Number , Column Number)
The colurIndex identifys colour, if you record a macro of you colouring cells it will give you a fair idea of what numbers you require for your colour range.
Hope this helps.
I have purely used this to check on cells E19:E20 but is easily altered;
Code:
For i = 19 To 20
If Cells(i, 5).Interior.ColorIndex = 3 Then
Cells(i, 6) = 1
Else
If Cells(i, 5).Interior.ColorIndex = 6 Then
Cells(i, 6) = 2
End If
End If
Next ithe for i is the range of rows you are checking with 19 To 20 being row numbers.
Cells(i , 6) equates to Cells(Row Number , Column Number)
The colurIndex identifys colour, if you record a macro of you colouring cells it will give you a fair idea of what numbers you require for your colour range.
Hope this helps.
Upvote
0
Hi, you have to indicate what you are talking about when you write, "cell color".
Are we talking about the font? Or are we talking about cell shading with regards to pattern: FORMAT/CELL/[PATTERN TAB]
If we are talking about cell shading, then there is no direct Excel function that will allow you to determine the color.
However, creating a custom function is quite easy. Put the following in a module in your worksheet:
Function WhatColor(rngA As Range) As Integer
WhatColor = rngA.Interior.ColorIndex
End Function
Your IF statement on your worksheet would then be =IF(WhatColor(A1)=54,"purple","not purple")
You'll find though that if you change the color of the cell pattern, your spreadsheet will not calculate. Thus the function WhatColor does not update. Even if you declare the function volatile. It will only update when you actually change a value in any cell in the workbook.
This may not fit what you need and you may want to think of an alternative way to do what you want.
Good luck.
Are we talking about the font? Or are we talking about cell shading with regards to pattern: FORMAT/CELL/[PATTERN TAB]
If we are talking about cell shading, then there is no direct Excel function that will allow you to determine the color.
However, creating a custom function is quite easy. Put the following in a module in your worksheet:
Function WhatColor(rngA As Range) As Integer
WhatColor = rngA.Interior.ColorIndex
End Function
Your IF statement on your worksheet would then be =IF(WhatColor(A1)=54,"purple","not purple")
You'll find though that if you change the color of the cell pattern, your spreadsheet will not calculate. Thus the function WhatColor does not update. Even if you declare the function volatile. It will only update when you actually change a value in any cell in the workbook.
This may not fit what you need and you may want to think of an alternative way to do what you want.
Good luck.
Upvote
0
