# Changing color in macro

#### ecarney14

##### New Member
Hey I am trying to use the If function to change the color of the cell in a macro, for instance.

If(Sheet1!A2>0, (show Sheet1!A2 in color), Sheet1!A2)

TO explain better, if a previous cell is higher than 0, make the new cell green, if not leave it the same as before.

I want this to show results better.

Any help will be great thanks!

### Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
What do you mean by previous cell? The references in your IF are all the same, indicating that there is no difference in reference between "previous" and "new" cell, which doesn't make sense.

It is a previous if statement, that I calculated. Basically the 3rd sheet of my workbook has
If ((and slope>0, r^2>.9)), r^2, "-"

Basically, if the slope is higher than 0, and the r^2 value is higher than .9, show the r^2 value in the cell.
Now I want to change the color of the cell, so that if the r^2 value is from .90 to .93 it would be shaded blue, from .93 to .95 it would be green... and so on.

Thanks for your help, i am really new to writing macros in general so this is all steps in the dark for me.

Now I want to change the color of the cell, so that if the r^2 value is from .90 to .93 it would be shaded blue, from .93 to .95 it would be green... and so on.
You have mentioned 2 colours for the cell. How many more are covered by the phrase "... and so on."? You can colour cells with 3 different conditions using Conditional Formatting by the way.

I think i could have just figured it out with using conditional formatting, thanks so much!

As just a really basic question. Say I have a condition/formula and i want all the columns A-CS from rows 2-999 all to have the same way. What is the shorter way in a macros to do it rather than to type out

Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B999"), Type:=xlFillDefault
Range("B2:B999").Select
Range("C2").Select
Selection.AutoFill Destination:=Range("C2:C999"), Type:=xlFillDefault
Range("C2:C999").Select
Range("D2").Select
Selection.AutoFill Destination:=Range("D2:D999"), Type:=xlFillDefault
Range("D2:D999").Select

And so on...

As you can tell... I am just a sad 20 year old learning excel macros for the first time.

Thanks again!

Select the entire range and then add the Conditional Formatting.

How would you write it in the macro? other than each line out like i mentioned

First of all, what is the formula for the condition in the Conditional Formatting? Have you done a "macro record" to see what it looks like in VBA code? Have you done a "macro record" before selecting the whole range and then doing the Conditional Formatting?

I have done macro records (that's basically how i started with everything and learned from there)

my formula for the cells is

ActiveCell.FormulaR1C1 = _
"=IF((AND(Sheet2!RC[-1]>0, Sheet2!RC[48]>0.9)), Sheet2!RC[48],""-"")"

Then I go and apply both that formula and the conditional formatting to each the range of columns and rows.

That's not what I asked. I asked for the formula in the Conditional Formatting. I assumed you'd chosen "Formula Is" in Conditional Formatting, and entered a formula. If not, then it should be even easier than I thought.

You can enter formulae, and apply conditional formatting to an entire range of rows and columns, and not just one column at a time.

Replies
3
Views
120
Replies
1
Views
124
Replies
4
Views
112
Replies
10
Views
453
Replies
2
Views
310

1,214,262
Messages
6,118,550
Members
448,835
Latest member
Profast123

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