Change Row height and colour based on value

Andyjk1984

New Member
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

MrExcel MVP, Moderator
In that case how about
Code:
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
 

Fluff

MrExcel MVP, Moderator
Glad to help & thanks for the feedback
 

Andyjk1984

New Member
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

MrExcel MVP, Moderator
How about
Code:
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

New Member
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?
 

Some videos you may like

This Week's Hot Topics

Top