Conditional formatting question

steve6501

Board Regular
Joined
Jun 18, 2004
Messages
89
Hi

I have an excel form sent out to clients which i wish to add some control of their input.

I wish to add part of a row which when an entry is made in say col A, the next five columns will turn a different colour until a check box at the end of the row is checked and agreed by their Manager. The lack of this check will show that theitem has not been checked.

Is it possible to do this with conditional formatting? I have tried but cant get it to work correctly.

Thanks

Steve
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Hey,

Assuming that you created an ActiveX "Checkbox1" ,You Should Insert The Following Code into The sheet code

Code:
Private Sub CheckBox1_Click()
ItemChecked
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
Range("b:f").Interior.Color = vbRed
End Sub

and you enter this one In a regular module

Code:
Sub ItemChecked()
Range("b:f").Interior.Pattern = xlNone
End Sub

I hope this code will help you.

ZAX
 
Last edited:
Upvote 0
Hi.

Thanks for the reply.

I really wanted to do this without a macro. The reason being that the PC's being used are 'locked' to disable macros.

Could this be done with conditional formatting and a cell to be checked at the end of the row, please

Thanks

Steve
 
Upvote 0
I am also looking for help. I have spent hours reading, researching, testing without success. My problem:
I have two values a user will choose to enter; 30 or 50 in a cell ($D6) and another value in cell ($F6). In plain language,
if ($D6) equals 30 and ($F6) is less than 12 then ($F6) is RED; if ($D6) is 50 and ($F6) is less than 25 then ($F6) is RED.

I thought conditional formatting would be fairly easy for me but ...any help greatly appreciated!
 
Upvote 0
Steve,
Conditional Formatting can change the format (colour) based upon a formula. See; "Use formula to determine which cells to format"
Use the AND formula in the condition ("Format values where this formula is true:") for example; =(AND("say col A value","check yes/no"). This will need complex formulas cross referencing the "col A" and the check box for the range of cells. Use the "$" sign to lock references.
All this requires the formula to recognise the check box result. Go to "Format Control" (right click the checkbox) and under control tab, nominate a linked cell.
ensure the conditional formating does not lock the cell referenced and can move when you copy the cell formatting across your range to be effected.
So, "col A" gives one result and the "linked cell" gives another. when they are both true the Conditional Formatting will colour your cell(s).
Hope that helps,
Bruce
 
Upvote 0
buz;
($D6) equals 30 and ($F6) is less than 12
Use: =AND($D6=30,$F6<12)

You'll need multiple conditions or try this:
($D6) is 50 and ($F6) is less than 25

=OR(AND($D6=30,$F6<12),AND($D6=50,$F6<25))

If either is positive, your RED format suits. If you want different colours, you'll need multiple formats.

I haven't tested it but I think that's what you are trying.

Keep an eye on the Cells the the formatting "Applies To:" to make sure they are referencing correctly if you are copying the formatting across an area.

Good luck,
 
Upvote 0
buz;
($D6) equals 30 and ($F6) is less than 12
Use: =AND($D6=30,$F6<12)

You'll need multiple conditions or try this:
($D6) is 50 and ($F6) is less than 25

=OR(AND($D6=30,$F6<12),AND($D6=50,$F6<25))

If either is positive, your RED format suits. If you want different colours, you'll need multiple formats.

I haven't tested it but I think that's what you are trying.

Keep an eye on the Cells the the formatting "Applies To:" to make sure they are referencing correctly if you are copying the formatting across an area.

Good luck,

Brucesw,
That's it! I was close but I used "OR" between the condition statements. Thank you for your help!
Buz
 
Upvote 0

Forum statistics

Threads
1,202,963
Messages
6,052,824
Members
444,602
Latest member
Cookaa

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top