# VBA for Conditional Formatting

#### bthomas

##### Board Regular
Hi!!!

I posted this to another forum on Monday with no luck, so I was hoping a broader audience would help me get this answered. I appreciate any info you an give!

Link to other post: Need VBA help for Conditional Formatting

I have a very large spreadsheet that shows sales data influence between two customers. Whether it be the customers billing location and shipping location or a contractor/oem and the end user. In the spreadsheet, it gives the influence customer (where \$\$ are flowing to for the sales guy to get credit) and the non-influence customer. When we receive the file the influence is pre-determined, however, I have a formula that automatically flips the influence to the non-influence customer based on certain criteria.

Now, in this sheet I created a conditional format that highlights the row when clicked. The cells change color based on the criteria result in one cell.

This is what the conditional formatting for the row looks like without VBA.

Formula =CELL("row")=CELL("row",A9) Pertains to Cells =\$A\$9:\$X\$10001 color bright blue cell highlight
Formula =AND(CELL("row")=CELL("row",XFA9),OR(AND(\$B9<>\$J9,\$A9=""),AND(\$A9<>\$J9,\$A9>""))) Pertains to Cells =\$I\$9:\$N\$10001 color red cell white font
Formula =AND(CELL("row")=CELL("row",A9),OR(AND(\$B9=\$J9,\$A9=""),\$A9=\$J9)) Pertains to Cells =\$I\$9:\$N\$10001 color green black font

Is there a way to convert this to vba?

Thanks so much!!!!​

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

#### DeBeuz

##### Active Member
Hi,

Did you try this:
Start marcro recording.
Select a cell
Set up the required conditional formatting with the formula's and colors etc.
Stop the macro
Check out the code generated and modify the selection part.
It always worked for me.

Succes.

Paul

#### bthomas

##### Board Regular
Hi,

Did you try this:
Start marcro recording.
Select a cell
Set up the required conditional formatting with the formula's and colors etc.
Stop the macro
Check out the code generated and modify the selection part.
It always worked for me.

Succes.

Paul

Hi DeBeuz,

The only issue is it has to change color based on a condition, so I am not sure how to get that portion into the vba. I tried recording the condition, but for some reason you can't record a formula when creating a condition.

#### DeBeuz

##### Active Member
Hi,

I have done this before is some other workbooks of mine.
This is what the recorded code looked like:
Code:
``````Range("E3:E20000").Select
Selection.FormatConditions.Delete
"=(SEARCH(""Total"",D3))*(SUM(IF((\$D\$3:\$D\$21000=OFFSET(E3,-1,-1))*(\$B\$3:\$B\$21000>TODAY()-\$B\$1),1,0))=0)"
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = True
.ColorIndex = 3
End With``````
You will have to change the settings between the "with" and "end with" to set the color.
At the 4th line you see an example of the setup for a formula.

Paul

#### bthomas

##### Board Regular
Hi,

I have done this before is some other workbooks of mine.
This is what the recorded code looked like:
Code:
``````Range("E3:E20000").Select
Selection.FormatConditions.Delete
"=(SEARCH(""Total"",D3))*(SUM(IF((\$D\$3:\$D\$21000=OFFSET(E3,-1,-1))*(\$B\$3:\$B\$21000>TODAY()-\$B\$1),1,0))=0)"
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = True
.ColorIndex = 3
End With``````
You will have to change the settings between the "with" and "end with" to set the color.
At the 4th line you see an example of the setup for a formula.

Paul

Yes, that might work! Thanks so much!!!!

Replies
3
Views
253
Replies
10
Views
200
Replies
6
Views
198
Replies
4
Views
208
Replies
13
Views
396

1,195,588
Messages
6,010,600
Members
441,558
Latest member
lambierules

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back