×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Conditional formatting based on another cell
2

Conditional formatting based on another cell

Conditional formatting based on another cell

(OP)
Using search it seems that there isnt a way to do this, but people seem to find very clever ways of getting round the problem, so maybe you can help here.

I have made some design spreadsheets for our company that have to be used in the states as well and therefore I have built unit conversion into them. Thing is this means to fully display the diameter of say a 0.25" pipe I have to show the same decimals for a 800mm pipe.(i.e. 800.00mm) this not only looks stupid but shows unrealistic accuracy of the numbers.

Is there a way where based on unit selection (drop down menu giving target cell value of 1 for inches or 2 for mm) I can change the number of decimals displayed?

Thanks in advance

RE: Conditional formatting based on another cell

2
Hi JKaen

I take it you have a drop down menu on your spreadsheet linked to a cell, outputing 1 for Imperial and 2 for Metric.

If you right click on the drop down menu and choose assign macro, this will open the change_event for that control.  This means the following code will execute every time the drop down is used.

In the DropDown1_Change() sub enter the folling code

Sub DropDown1_Change()

' Test the value of the cell linked to the dropdown
' 1 for Imperial
' 2 for Metric
If ThisWorkbook.Sheets("Sheet1").Range("A3") = 1 Then
    ' If imperial change the cell format for A1 to 4 deciaml places
    ThisWorkbook.Sheets(2).Cells(1, 1).NumberFormat = "0.0000"
Else
    ' If metric change the cell format for A1 to 3 deciaml places
    ThisWorkbook.Sheets(2).Cells(1, 1).NumberFormat = "0.000"
End If

End Sub

RE: Conditional formatting based on another cell

(OP)
Thanks, I will try that.

RE: Conditional formatting based on another cell

(OP)
Well it worked, so thanks for that, but it seems to have thrown a new problem up. Due to the fact some of my users dont really know what they are doing I have to protect my spreadsheet, and it seems that for this macro to work the target cells need to be unprotected.

Using the record on cursor thing for the macro I have found how to protect/unprotect in the macro, however it doesnt protect with a password, is there anyway to add a password as part of the condition of protect/unprotect?

RE: Conditional formatting based on another cell

Add this line above the if statement in the dropdown_change module.

ThisWorkbook.Sheets("Mysheet").UnProtect ("MyPassword")

Then add this line after the if statement

ThisWorkbook.Sheets("Mysheet").Protect ("MyPassword")


Remember to set the cell properties to locked for the cells you wish to protect.

RE: Conditional formatting based on another cell

(OP)
Yep, works great, thanks

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources