How would I apply conditional formatting to a cell's value rather than the appearance of that value?

Question

As the title suggests, I am working with a conditional formatting problem.

Conditional formatting allows me to, based on a formula, change the appearance of a cell (fill-in colour, text colour), but is there any way to change the text itself?

Edit: Specifically, I have a list of cell locations in Sheet1 B1:B100, and on Sheet 2 an array (A1:Z26) those locations refer to. Using the following forumla in conditional formatting,

=MATCH(CELL("address",A1),Sheet2!$B:$B,0)

I would then wish the cell, if TRUE, to change (possibly through an INDEX/MATCH), to show the value on Sheet 1 in the same row, at a different column, A.

So for example, if Sheet 1, A1=John Smith B1=$Z$26 then at Sheet 2 Z26 would = "John Smith."

Do you want the Number Formatting instead? (Ctrl + 1, or Right Click then "Format Cells") — Chronocidal, Jun 10 '19 at 10:11
Possible duplicate of [How to replace text of a cell based on condition in excel](https://stackoverflow.com/questions/11399111/how-to-replace-text-of-a-cell-based-on-condition-in-excel) — FAB, Jun 10 '19 at 10:13
You can create a formula, the result of which is the 'format' of another cell? See `=TEXT(Value you want to format, "Format code you want to apply")` — CLR, Jun 10 '19 at 10:23
@FAB I would not say so, as the solution there allows me to change the text manually, but I am seeking a solution that can be replicated by reference to another cell. — Matthew Keracher, Jun 10 '19 at 11:04
Could you give us an example of what is in the cell now - and how you'd want that to change? — CLR, Jun 10 '19 at 11:06
@MatthewKeracher sorry mate, but for that the details you have in your question, that solution is as good as any other, is just a matter of applying that knowledge to your very specific situation (which you are not making clear in your question, so...). — FAB, Jun 10 '19 at 11:10
@FAB I understand, my apologies, I have added some context to make the specifics of my problem clearer. — Matthew Keracher, Jun 10 '19 at 11:13
What you've asked for there, in your example, is just a (conditional) formula, in Sheet2 Z26. No formatting required. — CLR, Jun 10 '19 at 11:26

score 0 · Answer 1 · answered Jun 11 '19 at 11:58

I was able to do it with the following custom function entered in every cell in the range Sheet2!$A$1:$Z$26, like so: =Findvalue(Sheet1!$B$1:$B$26), with the below data entered into sheet1

Function FindValue(ByVal searchRange As Range)

    Dim formulaInCell As Range
    Dim cll As Range

    Set formulaInCell = Application.Caller

    Set cll = searchRange.Find(What:=formulaInCell.Address, lookat:=xlWhole)

    If cll Is Nothing Then

        FindValue = ""

    Else

        Set cll = cll.Offset(0, -1)
        FindValue = cll.Value

    End If

End Function

How would I apply conditional formatting to a cell's value rather than the appearance of that value?

1 Answers1