Increasing row height if condition is met using VBA.

[hubble101]

Hey team, my VBA knowledge is extremely little, so hoping I could get some help here!

I currently have a worksheet that automatically generates "Total" in a cell when a certain condition is met through an IF function.

I now want to increase the row height, font and font size in the row that the word "Total" appears.

In the worksheet, the word "Total" can be anywhere from C9:C500, I would like the row height to be 30, the font to be Times New Roman, and the font size to be 20.

If there is any further information you require, please let me know!
thanks

 

[mrshl9898]

Like this?

Sub Macro1()


Dim rownum As Long


rownum = 9


On Error GoTo nextrow
Do Until rownum = 501
    If Cells(rownum, 3).Value = "Total" Then
        With Rows(rownum)
        .RowHeight = 22.5
        .Font.Name = "Times New Roman"
        .Font.Size = 20
        End With
    End If
nextrow:
rownum = rownum + 1
Loop


End Sub

 

[hubble101]

Like this?

Sub Macro1()


Dim rownum As Long


rownum = 9


On Error GoTo nextrow
Do Until rownum = 501
    If Cells(rownum, 3).Value = "Total" Then
        With Rows(rownum)
        .RowHeight = 22.5
        .Font.Name = "Times New Roman"
        .Font.Size = 20
        End With
    End If
nextrow:
rownum = rownum + 1
Loop


End Sub

Thank you so much for getting back to me so quickly, this runs perfectly!

Is there a way to have this automatically run everytime an edit is made, rather than having to run manually?

 

[hubble101]

Thank you so much for getting back to me so quickly, this runs perfectly!

Is there a way to have this automatically run everytime an edit is made, rather than having to run manually?

And also to set the row height to the rows this criterion isn't met to 15 whenever it is run?

 

[Michael M]

This may be quicker.....but why rerun the code every time a cell changes ??

Sub MM1()
Dim FR As Long
With Sheets("sheet1")
    Set FindRow = .Range("C:C").Find(What:="Total", LookIn:=xlValues)
End With
FR = FindRow.Row
    With Rows(FR)
            .RowHeight = 30
            .Font.Name = "Times New Roman"
            .Font.Size = 20
    End With
End Sub

 

[Michael M]

That means you will need to "Unformat" each row each time to reformat the cells to the required format....
Why not setup the page with 15 piont size as the default ???

 

[hubble101]

That means you will need to "Unformat" each row each time to reformat the cells to the required format....
Why not setup the page with 15 piont size as the default ???

Hi Michael, thank you for the suggestion! As mentioned, excel is definitely not my strong suit

Perhaps I could instead run it everytime the sheet is selected? This will be used by other employees who have even less excel knowledge than me, they are basically inputting data into one sheet, and having it format on another. I feel that having it run the code everytime the sheet is selected would be ideal for this, if it is at all possible

 

[Michael M]

OK, in that case you need to define for us what are the default formats, so that when the sheet is activated it returns everything to "normal" first, then runs the find code !!

 

[hubble101]

Hi Michael, would you hate me if I said I didn't know how to check this? Haha

As I have been attempting to format this sheet in an aesthetic way, there are many different column heights.

If I remove everything confidential and send the sheet through, would that be a help?

Thanks in advance!

 

[Michael M]

Sorry can't accept mailed workbooks !!
But if the cells change, won't the total still be the Total wherever it is in the column. ????