Fill cell based on cell value

K

Kesbutler

I have a number in column A and a number in column B. If column B is less
than column A, I want to fill the cell with a color. I tried conditional
formatting but it won't let me apply the formatting to all the cells in
column B at one time, I have to do each cell individually. Is there a way
(macro, formula?) that I can apply this rule to the entire column?

Thanks!
 
J

Joel

use paste special with format xlPasteFormats

Range("B1").Copy
Range("B1:B100").PasteSpecial(xlPasteFormats)
 
K

Kesbutler

Thanks. Is there a way to have it automatically shade without having to
manually run the macro?
 
K

Kesbutler

I was wondering if there was a way to have column B automatically update as
data is updated? One week it might be less than and the next it might not.
 
J

Joel

Use a worksheet change

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Application.Intersect(Target, Columns("B")) Is Nothing Then
Range("B1").Copy
Target.PasteSpecial (xlPasteFormats)

End If
End Sub
 
K

Kesbutler

this sort of worked but it compared all the rows in column B with the first
row in column A instead of incrementing. I need the following. If any cellin
column B is less than the corresponding cellin column A then fill cell.
row A B
1 1 2 not shaded
2 2 1 shaded
3 5 4 shaded
4 4 5 not shaded

Thanks much!
 
J

Joel

You conditional format formula must have a dollar sign in the formula.
Remove the dollar sign in the conditional format formula in B1.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top