Formatting a cell with Text based off the color of another cell

[aadam0906]

I want to have cell A auto populate either 'PB' or 'AF' based off the color of cell B. Can anyone think of a way to do this, I did try conditional formatting but I could not figure out to make it work.

 

[Max1616]

Question: What is dictating the color of the row? Is it conditional formatting, or is this being done via a manual change?

The reason I ask, is that if this is being done via conditional formatting, you can use an IF function using the same logic populating the conditional formatting, to then populate AF/PB.

Now, on the other hand, if these rows are being colored by a manual process, then you will need to use VBA. I can put together some VBA For you, but I'll need to know the Color code that you are using for Yellow/Blue. To do this, click a yellow cell and run this VBA:

Sub ColorCode()

MsgBox ActiveCell.Interior.Color

End Sub

Then do the same when selecting a Blue cell. And provide me those numbers.

 

[Max1616]

Also, to expand on my previous comment. Once you have those color codes, I'd create a function using VBA:

Function COLORVALUE(x As Range)

COLORVALUE = "ColorNotFound"
If x.Interior.Color = "9985057" Then COLORVALUE = "PB" 'Blue Color Value
If x.Interior.Color = "65535" Then COLORVALUE = "AF" 'Yellow Color Value

End Function

Where you replace those color numbers (9985057,65535) with the color values provided by my previous code. This will create a new function in Excel:

=ColorValue(F4)

Where it will return "PB" if the interior color of that cell is Blue, and "AF" if it is yellow.

I hope this helps!

 

[aadam0906]

Question: What is dictating the color of the row? Is it conditional formatting, or is this being done via a manual change?

The reason I ask, is that if this is being done via conditional formatting, you can use an IF function using the same logic populating the conditional formatting, to then populate AF/PB.

Now, on the other hand, if these rows are being colored by a manual process, then you will need to use VBA. I can put together some VBA For you, but I'll need to know the Color code that you are using for Yellow/Blue. To do this, click a yellow cell and run this VBA:

Sub ColorCode()

MsgBox ActiveCell.Interior.Color

End Sub

Then do the same when selecting a Blue cell. And provide me those numbers.

Also, to expand on my previous comment. Once you have those color codes, I'd create a function using VBA:

Function COLORVALUE(x As Range)

COLORVALUE = "ColorNotFound"
If x.Interior.Color = "9985057" Then COLORVALUE = "PB" 'Blue Color Value
If x.Interior.Color = "65535" Then COLORVALUE = "AF" 'Yellow Color Value

End Function

Where you replace those color numbers (9985057,65535) with the color values provided by my previous code. This will create a new function in Excel:

=ColorValue(F4)

Where it will return "PB" if the interior color of that cell is Blue, and "AF" if it is yellow.

I hope this helps!

View attachment 115063

Thank you, just when I think I am pretty Excel savvy. I have never used VBA so I will have to get myself up to speed with that.