macro to clear rows that are conditionally filled with a color

[vettepwr]

I am trying to convert a sheet I made on Google Sheets over to Excel to share with some co-workers. Obviously my google apps code doesnt work in excel and I not familiar with vba that much.

I need to be able to create a macro that I can assign to a button that will clear the contents of rows that are conditionally formatted with a filled color.

This is the code I was using in google, each function being linked to 3 separate buttons.

function CLEARAUTH() {
var sheet = SpreadsheetApp.getActive().getSheetByName('CLAIMS TRACKER');
var range = sheet.getDataRange();
var bgColors = range.getBackgrounds();
for (var i=0; i<bgColors.length; i++) {
for (var j=0; j<bgColors.length; j++) {
if (bgColors[j] === '#6aa84f') {
range.getCell(i+1,j+1).clearContent();
}
}
}
}
function CLEARDENIED() {
var sheet = SpreadsheetApp.getActive().getSheetByName('CLAIMS TRACKER');
var range = sheet.getDataRange();
var bgColors = range.getBackgrounds();
for (var i=0; i<bgColors.length; i++) {
for (var j=0; j<bgColors.length; j++) {
if (bgColors[j] === '#b7b7b7') {
range.getCell(i+1,j+1).clearContent();
}
}
}
}
function CLEARINACTIVE() {
var sheet = SpreadsheetApp.getActive().getSheetByName('CLAIMS TRACKER');
var range = sheet.getDataRange();
var bgColors = range.getBackgrounds();
for (var i=0; i<bgColors.length; i++) {
for (var j=0; j<bgColors.length; j++) {
if (bgColors[j] === '#0000ff') {
range.getCell(i+1,j+1).clearContent();
}
}
}
}

What I need is a VBA version to do the same thing.
I need the macro to look at rows from cells B3 - T100 check for the color assigned by the conditional formatting and then clear the contents of the rows but only the data in Columns B-T Rows 3 - 100
I have been trying to figure this out for hours now and just cant seem to get it working. Any help would be greatly appreciated.

 

[Fluff]

Hi & welcome to MrExcel.
Will the entire row be filled with the colour, or just some cells?
If just some cells, should only those be cleared or the entire row?

 

[vettepwr]

Hi & welcome to MrExcel.
Will the entire row be filled with the colour, or just some cells?
If just some cells, should only those be cleared or the entire row?

The conditional formatting only applies a fill color to columns B-T and only the data in the cells from B-T needs cleared.

 

[Fluff]

Yes but will all cells from B to T on a particular row be filled, or only some?

 

[vettepwr]

Yes but will all cells from B to T on a particular row be filled, or only some?

All cells from b to t will be filled with the same color. Sry for confusion.

 

[Fluff]

Ok, how about

Sub vettepwr()
   Dim Cl As Range
   
   For Each Cl In Range("B3:B100")
      If Cl.DisplayFormat.Interior.Color = 5220458 Then Cl.Resize(, 19).ClearContents
   Next Cl
End Sub

 

[vettepwr]

Ok, how about

Sub vettepwr()
   Dim Cl As Range
  
   For Each Cl In Range("B3:B100")
      If Cl.DisplayFormat.Interior.Color = 5220458 Then Cl.Resize(, 19).ClearContents
   Next Cl
End Sub

I am going to try that here shortly. Will post back with results. Thank you so much

 

[vettepwr]

Awesome that worked perfectly. Now I see where I can change the color its searching for but I am not sure how you converted my hex color code to the number you used in the code. I am going to go see if I can figure that part out on my own, unless you happen to reply back before I do.

Ill just need to edit that color code to make the other two macros.

 

[vettepwr]

Yep got it. useda hex to rgb converter and used RGB(xx, xx, xx) format

Thanks so much again. Im going to go work on finishing this sheet to excel conversion.

 

[Fluff]

Glad to help & thanks for the feedback.