VBA code to change fill color of a cell if it contains any string

[wrecclesham]

I want to conditionally format cells in the range A1:A200 that contain any string.

If a cell is blank, no formatting should apply to that cell, but once any string is entered in a given cell, that individual cell should have its fill changed.

Any ideas?

 

[Fluff]

Hi & welcome to MrExcel.
Select A1:A200 & use this formula
=A1<>""

 

[wrecclesham]

So I've actually got conditional formatting working using that formula already.

It works great but the problem is that the rules start to become fragmented and messy when I move data around by dragging and dropping or copying and pasting, which I do quite a lot.

Even copying and pasting between different conditional formatting affects my rules, unless I remember to choose the paste "values" option, instead of regular paste, I end up with multiple rules, with different values in the "applies to" field. The conditional formatting should not follow my data when I drag it into another column.

I basically just want to replicate the same functionality as these conditional formatting rules using a macro.

Here's how it looks right now. I'm trying to figure out what VBA code I could replace it with.

Does that make sense?

 

[Fluff]

Are the values in A1:A200 & B1:B200 hard values, or the result of formulae?

 

[wrecclesham]

Hard values.

 

[Fluff]

In that case try

Sub wrecclesham()
   With Range("A1:A200")
      .FormatConditions.Delete
      .FormatConditions.Add xlExpression, , "=A1<>"""""
      .FormatConditions(1).Interior.Color = [COLOR=#ff0000]45678[/COLOR]
   End With
End Sub

Change the value in red to suit
and do the same for col B

 

[wrecclesham]

I currently have a few lines of VBA code which dynamically sort columns A and B alphabetically, whenever anything changes in either of them.

If I want to add your code to my existing code, would I just paste one block beneath the other like this? Just wondering if there's any extra syntax I'm missing?

Private Sub Worksheet_Change(ByVal Target As Range)

    Range("A1:A200").Sort Key1:=Range("A1"), _
      Order1:=xlAscending, Header:=xlNo, _
      OrderCustom:=1, MatchCase:=False, _
      Orientation:=xlTopToBottom
      
          Range("B1:B200").Sort Key1:=Range("B1"), _
      Order1:=xlAscending, Header:=xlNo, _
      OrderCustom:=1, MatchCase:=False, _
      Orientation:=xlTopToBottom
      
End Sub

Sub wrecclesham()
   With Range("A1:A200")
      .FormatConditions.Delete
      .FormatConditions.Add xlExpression, , "=A1<>"""""
      .FormatConditions(1).Interior.Color = 45678
   End With
End Sub

 

[Fluff]

If you want it as part of a change event, add this just before the End Sub

   With Range("A1:B200")
      .Interior.Color = xlNone
      .Resize(, 1).SpecialCells(xlConstants).Interior.ColorIndex = 22
      .Offset(, 1).Resize(, 1).SpecialCells(xlConstants).Interior.ColorIndex = 36
   End With

 

[wrecclesham]

For some reason I can't get that code to work.

I've cleared my original rules and that code just doesn't seem to be affecting my formatting.

Maybe I'm missing something.

 

[Fluff]

How have you added it to your code?