Find string in range and change cell format to reference cell format

[Luke777]

Hi all,

I know how to search a range for a specific string and then format (fill colour, font, align etc) cells that have the string, but is it possible to use a 'reference' cell?

So for example, the code searches the range for the string contained in the reference cell and where it finds it, uses the reference cells formatting (colour etc not time/date/currency format).

I figured this method would be more 'user friendly' for any changes - so if the string changes it still works (as long as the string still crops up in the target range) and any formatting can be altered simply by changing the reference cell rather than mucking about in VBA.

 

[BSALV]

something like this

Sub test()
     Set c1 = Range("a1")                                       'cell with the formats
     Set c2 = Range("A2")                                       'cell with the value

     With Range("D1")                                           'the cell you found with your search
          c1.Copy                                               'copy the reference cell
          .PasteSpecial xlPasteFormats                          'paste the formats
          .Value = c2.Value                                     'fill the value
     End With
End Sub

 

[Alex Blakenburg]

I think your issue is how do you implement the change.

Say you change the color to a new color
You no longer have access to the old color to go looking for it to make the change. You would have to have the search criteria in a cell as well.
Note: a change event does not seem to recognise a colour change as a change event

Would you perhaps be better off using conditional formatting and then you can do something like this:-
Where A4 is any cell in the range that has the conditional formatting applied and
C4 is the reference cell

Range("a4").FormatConditions(1).Interior.Color = Range("c4").Interior.Color