Macro to add font color and cell color + to add a simple formula in a specific range

[hnt007]

Hi! Thank you very much for helping me out with this... I'm trying to have a macro (that I would link to a button called RESET) that will apply a bunch of things to the range D8:D1000:

- adding the formula =L8 in D8, =L9 in D9, =L10 in D10 etc until =L1000 in D1000
- font color > black
- cell color > none

 

[johnnyL]

How about:

Sub AddFormulas()
'
    Range("D8").Formula = "=L8"
    Range("D8:D1000").FillDown
    Range("D8:D1000").Font.ThemeColor = xlThemeColorLight1
    Range("D8:D1000").Interior.Pattern = xlNone
End Sub

 

[hnt007]

How about:

Sub AddFormulas()
'
    Range("D8").Formula = "=L8"
    Range("D8:D1000").FillDown
    Range("D8:D1000").Font.ThemeColor = xlThemeColorLight1
    Range("D8:D1000").Interior.Pattern = xlNone
End Sub

This looks very promising, thank you! Is there a way to add a line to also remove content from B8:B1000?

 

[johnnyL]

Sub AddFormulas()
'
    Range("D8").Formula = "=L8"
    Range("D8:D1000").FillDown
    Range("D8:D1000").Font.ThemeColor = xlThemeColorLight1
    Range("D8:D1000").Interior.Pattern = xlNone
    Range("B8:B1000").ClearContents
End Sub

 

[hnt007]

Sub AddFormulas()
'
    Range("D8").Formula = "=L8"
    Range("D8:D1000").FillDown
    Range("D8:D1000").Font.ThemeColor = xlThemeColorLight1
    Range("D8:D1000").Interior.Pattern = xlNone
    Range("B8:B1000").ClearContents
End Sub

WOW Johnny this is amazing!! The only problem I have is that it's messing up my borders, see below for before and after running the macro

 

[johnnyL]

Which code does that? The first code or the second code?

 

[hnt007]

Which code does that? The first code or the second code?

The second one, thanks!!

 

[johnnyL]

I don't see how.

But anyways, What type of borders do the cells normally have? Your picture doesn't appear to display a default border, but that could just be the picture.

Can you upload a sample file that we can look at? Then provide the link to it?

 

[hnt007]

I don't see how.

But anyways, What type of borders do the cells normally have? Your picture doesn't appear to display a default border, but that could just be the picture.

Can you upload a sample file that we can look at? Then provide the link to it?

Thanks for you help, here is a copy of my file: !!MDP Rev_May_5_2022.xlsm

So basically you fill out column B with filenames and then press the button CLICK HERE to see if you have misspelled words etc, and if you click on START OVER, everything gets erased which is great, but it messes up the look of that column D

Yeah it's a border that I picked, not a default one...

 

[johnnyL]

You fed me some faulty information, but how about:

Sub Reset()
'
    Range("D8:D1000").FormulaR1C1 = "=RC[8]"
    Range("D8:D1000").Font.ThemeColor = xlThemeColorLight1
    Range("D8:D1000").Interior.Pattern = xlNone
    Range("B8:B1000").ClearContents
End Sub