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
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
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
RE: Conditional formatting based on another cell
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
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