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.
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.