Hello..
I would like to have an If statement which says:
If cell B2 is highlighted yellow, “Pending”, else, “”
Is it possible to do an if statement based on the highlighting of a cell?
Thanks!
![]() |
Patch reliability is unclear. Unless you have an immediate, pressing need to install a specific patch, don't do it. |
SIGN IN | Not a member? | REGISTER | PLUS MEMBERSHIP |
Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » IF statment based on color of Cell (Excel 97/2000)
hi awckie
Don’t know what your constraints are, but you could tackle it the other way around, by using Conditional Formatting (under Format on the Main Menu Bar).
If you format all the cells in Column B to turn yellow, provided the value in Column C(?) is “Pending”, you would appear to be able to accomplish the same result.
HTH
It actually won’t work that way…
I have 600 rows of data. Someone went through and color coded the items based on whether it was pending, completed or in process. No where on any row is this information! So I either have to type the info in myself (which I don’t want to do) or find a way to do if statements based on color!
.. or, following Michael Reads’ suggestion, here’s a custom function which will return the colorindex, so you can process the number returned via =VLOOKUP(), =CHOOSE() or whatever:
Public Function GetFillColor(rngCell As Range) As Integer
Application.Volatile
GetFillColor = rngCell.Interior.ColorIndex
End Function
If it’s in the same WB or an add-in, usage will be: =getfillcolor(celladdress)
If it’s in Personal.xls, usage will be: =personal.xls!getfillcolor(celladdress)
As far as I know, you are unable to access the colorindex property without a custom function. The following code will do what you wish however (in column F, cells 1 to 20 – change as you wish):
Sub Pend() For Each cell In ThisWorkbook.Sheets("Sheet1").Range("F1:f20") With cell.Interior If .ColorIndex = 6 Then cell.Value = "Pending" If .ColorIndex 6 Then cell.Value = "" End With Next End Sub
CAUTION: This will overwrite whatever data might happen to be in the cell you are testing for the colorindex. You can change the code to put the “Pending” into another column if you wish.
You could run it as a result of the sheet activate or deactivate, a button, etc.
HTH,
You will need a User Defined Function to do this. The following function will return “Pending” if the cell passed as the argument is filled Yellow:
Public Function CkColor(oCell As Range) As String If oCell.Interior.ColorIndex = 6 Then CkColor = "Pending" End If End Function
However, if you are changing the cells fill color manually, without changing the cell value, that will not trigger a sheet recalculate. You will have to press F9 after changing the color to trigger the recalculate. If that is a problem, then the following code can be put into the Worksheet Selection Change event routine to cause the recalculate to trigger.
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(ActiveSheet.Range("C1:C10"), ActiveCell) Is Nothing Then ActiveSheet.Calculate End If End Sub
Donations from Plus members keep this site going. You can identify the people who support AskWoody by the Plus badge on their avatars.
AskWoody Plus members not only get access to all of the contents of this site -- including Susan Bradley's frequently updated Patch Watch listing -- they also receive weekly AskWoody Plus Newsletters (formerly Windows Secrets Newsletter) and AskWoody Plus Alerts, emails when there are important breaking developments.
Welcome to our unique respite from the madness.
It's easy to post questions about Windows 11, Windows 10, Win8.1, Win7, Surface, Office, or browse through our Forums. Post anonymously or register for greater privileges. Keep it civil, please: Decorous Lounge rules strictly enforced. Questions? Contact Customer Support.
Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.
Mastodon profile for DefConPatch
Mastodon profile for AskWoody
Home • About • FAQ • Posts & Privacy • Forums • My Account
Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts
Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.
Notifications