VBA for Conditional Formatting

[bthomas]

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!!!!​

 

[DeBeuz]

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]

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,

Thanks for your reply.

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]

Hi,

I have done this before is some other workbooks of mine.
This is what the recorded code looked like:

Range("E3:E20000").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
    "=(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.

Does this help you?

Paul

 

[bthomas]

Hi,

I have done this before is some other workbooks of mine.
This is what the recorded code looked like:

Range("E3:E20000").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
    "=(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.

Does this help you?

Paul

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