Change Row height and colour based on value

[Andyjk1984]

Hi great people
I have a work stream spreadsheet that I sort by date, the only thing that I'm.struggling with is the height of the rows based of column A containing the number 1, and then colouring the row (A to M) being dark grey

Can anyone help me?

 

[Fluff]

What height should the row be if it contains 1 in col A?

 

[Fluff]

Also is col A hard values, or formulae?

 

[Andyjk1984]

Height 33, and it's a formula based on a count

 

[Fluff]

In that case how about

Sub Andyjk1984()
   Dim Cl As Range
   
   For Each Cl In Range("A1", Range("A" & Rows.Count).End(xlUp))
      If Cl.Value = 1 Then
         Cl.RowHeight = 33
         Cl.Resize(, 13).Interior.ColorIndex = 16
      End If
   Next Cl
End Sub

 

[Andyjk1984]

Many thanks for your time and support.. its works perfectly appreciate it very much

 

[Fluff]

Glad to help & thanks for the feedback

 

[Andyjk1984]

Sorry, 1 more thing you can, is there a way that I can reset the other rows that dont meet the criteria it resets the rows height back to what it was (20)

 

[Fluff]

How about

Sub Andyjk1984()
   Dim Cl As Range
   
   For Each Cl In Range("A1", Range("A" & Rows.Count).End(xlUp))
      If Cl.Value = 1 Then
         Cl.RowHeight = 33
         Cl.Resize(, 13).Interior.ColorIndex = 16
      Else
         Cl.RowHeight = 20
      End If
   Next Cl
End Sub

 

[Andyjk1984]

Works a treat, now inhale a totally unexpected issue, so I have a macro that sorts it in date order which all the dates are in order expect the first 1, for example it goes 27/04/2020 to 22/04/2019, 06/05/2019,,,, proceeds in the desired order, I've checked the cells are all formatted the same, any ideas?