Home

Fill Cell Based on What color it is

%3CLINGO-SUB%20id%3D%22lingo-sub-731629%22%20slang%3D%22en-US%22%3EFill%20Cell%20Based%20on%20What%20color%20it%20is%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-731629%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20create%20either%20a%20macro%20or%20formula%20that%20I%20can%20put%20in%20column%26nbsp%3BC%20that%20will%20say%20if%20cell%20in%20corresponding%20row%20in%26nbsp%3Bcolumn%20B%20has%20no%20color%20then%20fill%20the%20contents%20in%20column%20C%26nbsp%3Bwith%20the%20last%20cell%20in%20column%26nbsp%3BB%20that%20has%20color%20grey.%20See%20before%2Fafter%20images.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENote%2C%20the%20metrics%20are%20not%20actually%20labeled%20that%20way%20so%20there%20is%20no%20wildcard%20option%20that%20would%20work%20to%20confirm%20if%20it%20is%20a%20metric%20title%20versus%20a%20name.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-731629%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-749189%22%20slang%3D%22en-US%22%3ERe%3A%20Fill%20Cell%20Based%20on%20What%20color%20it%20is%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-749189%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F369354%22%20target%3D%22_blank%22%3E%40DataDes%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20you%20want%20to%20do%20is%20not%20recommended%20practice.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20reason%20why%20is%20that%20changing%20the%20color%20of%20a%20cell%20manually%20does%20not%20cause%20Excel%20to%20recalculate.%20And%20using%20Conditional%20Formatting%20to%20change%20the%20color%20of%20a%20cell%20does%20not%20play%20well%20with%20user-defined%20functions%20that%20are%20testing%20cell%20color.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20are%20willing%20to%20accept%20those%20limitations%2C%20I%20wrote%20a%20function%20that%20will%20return%20the%20text%20from%20the%20first%20%22gray%22%20cell%20above%20the%20tested%20cell.%20You%20call%20it%20with%20a%20worksheet%20formula%20like%3A%3C%2FP%3E%3CP%3E%3DLastGrayCell(B2%2CB%241)%3C%2FP%3E%3CP%3EIn%20the%20above%20formula%2C%20B2%20is%20the%20cell%20being%20tested%20for%20color%2C%20while%20B1%20is%20a%20cell%20that%20has%20the%20(gray)%20color%20you%20are%20looking%20for.%20If%20you%20put%20the%20formula%20in%20cell%20C2%20and%20copy%20it%20down%2C%20it%20will%20return%20the%20text%20in%20the%20preceding%20cells%20in%20column%20B%20that%20match%20the%20desired%20color.%20When%20the%20column%20B%20cell%20has%20that%20color%2C%20an%20empty%20string%20(looks%20like%20a%20blank)%20is%20returned.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENote%20that%20the%20formula%20won't%20update%20until%20the%20user%20changes%20the%20value%20of%20any%20cell%20in%20the%20workbook.%20Also%2C%20the%20formula%20won't%20respond%20to%20colors%20set%20by%20conditional%20formatting%20at%20all.%20Finally%2C%20the%20formula%20won't%20color%20the%20cell%20in%20column%20C--but%20you%20could%20use%20Conditional%20Formatting%20for%20that%20purpose%20using%20the%20formula%20criteria%20%26lt%3B%26gt%3B%22%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFunction%20LastGrayCell(TestCell%20As%20Range%2C%20RefColorCell%20As%20Range)%20As%20Variant%3CBR%20%2F%3EDim%20cel%20As%20Range%2C%20rg%20As%20Range%3CBR%20%2F%3EDim%20i%20As%20Long%2C%20n%20As%20Long%2C%20RefColor%20As%20Long%3CBR%20%2F%3EApplication.Volatile%3CBR%20%2F%3ELastGrayCell%20%3D%20%22%22%3CBR%20%2F%3ERefColor%20%3D%20RefColorCell.Interior.Color%3CBR%20%2F%3EIf%20TestCell.Interior.Color%20%26lt%3B%26gt%3B%20RefColor%20Then%3CBR%20%2F%3ESet%20rg%20%3D%20Range(TestCell%2C%20TestCell.EntireColumn.Cells(1))%3CBR%20%2F%3En%20%3D%20rg.Cells.Count%3CBR%20%2F%3EIf%20n%20%26gt%3B%201%20Then%3CBR%20%2F%3EFor%20i%20%3D%20n%20-%201%20To%201%20Step%20-1%3CBR%20%2F%3EIf%20rg.Cells(i).Interior.Color%20%3D%20RefColor%20Then%3CBR%20%2F%3ELastGrayCell%20%3D%20rg.Cells(i).Value%3CBR%20%2F%3EExit%20For%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3ENext%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3EEnd%20Function%3C%2FP%3E%3C%2FLINGO-BODY%3E
DataDes
Visitor

I am trying to create either a macro or formula that I can put in column C that will say if cell in corresponding row in column B has no color then fill the contents in column C with the last cell in column B that has color grey. See before/after images.

 

Note, the metrics are not actually labeled that way so there is no wildcard option that would work to confirm if it is a metric title versus a name.

 

1 Reply

@DataDes 

What you want to do is not recommended practice.

 

The reason why is that changing the color of a cell manually does not cause Excel to recalculate. And using Conditional Formatting to change the color of a cell does not play well with user-defined functions that are testing cell color.

 

If you are willing to accept those limitations, I wrote a function that will return the text from the first "gray" cell above the tested cell. You call it with a worksheet formula like:

=LastGrayCell(B2,B$1)

In the above formula, B2 is the cell being tested for color, while B1 is a cell that has the (gray) color you are looking for. If you put the formula in cell C2 and copy it down, it will return the text in the preceding cells in column B that match the desired color. When the column B cell has that color, an empty string (looks like a blank) is returned.

 

Note that the formula won't update until the user changes the value of any cell in the workbook. Also, the formula won't respond to colors set by conditional formatting at all. Finally, the formula won't color the cell in column C--but you could use Conditional Formatting for that purpose using the formula criteria <>""

 

Function LastGrayCell(TestCell As Range, RefColorCell As Range) As Variant
Dim cel As Range, rg As Range
Dim i As Long, n As Long, RefColor As Long
Application.Volatile
LastGrayCell = ""
RefColor = RefColorCell.Interior.Color
If TestCell.Interior.Color <> RefColor Then
Set rg = Range(TestCell, TestCell.EntireColumn.Cells(1))
n = rg.Cells.Count
If n > 1 Then
For i = n - 1 To 1 Step -1
If rg.Cells(i).Interior.Color = RefColor Then
LastGrayCell = rg.Cells(i).Value
Exit For
End If
Next
End If
End If
End Function

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies