Conditional Formatting and Entering Dates with out dashes

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

First thank you to everyone who has answered a question on this site. It has
been very helpful.

Here are my issues.

I have a worksheet with 5 columns of data. Column B has a formula that
references column C. The formula looks like this =+C1+10. There are other
formulas in this column but I am only concerned with this one. Anytime this
occurs I want the cell to be bolded. I know to use conditional formatting and
I figured out that I must use the cell is equal to section. But I don't
remember how to write the formula so that it will mean Column C any Row + 10.

Second issue involves entering dates. I would like to be able to type 11207
and have it appear in a cell as 01/12/2007. I found an add-on but we are no
allowed to download items. Is there any other way to make this possible?

Once again thank you for all your help.
 
Question 1 - Conditional Formatting. Place your cursor in Cell B1. Now
highlight the entire column. Select Format -> Conditional Formatting ->
Selection is Equal to and in the Formula box put = C1 + 10. change the
format to Bold and you are good to go...

Question 2 - Entering Dates. Check out this link...
http://www.cpearson.com/excel/DateTimeEntry.htm
 
First question:
Select the range B1:B2345, then click on Format > Conditional Formatting,
then select "Cell Content Is", "Equal To", =C1+10. Note that if you had
selected a range B5:B234, then the formula should have been =C5+10. If you
want to always compare to cell C1, then use =$C$1+10.

Second question:
Press ALT+F11, then copy the code below in the sheet where you want to enter
the dates. I guessed that 11207 is 1-Dec-2007 and not 12-Jan-2007. Adapt the
code accordingly by swapping the digits 3 and 1 in the line of code Target.
Value = DateSerial(...

Private Sub Worksheet_Change(ByVal Target As Range)

Dim d As Long
Dim s As String

If Intersect(Target, Range("D2:D999")) Is Nothing Then ' <<< ADAPT THE
RANGE!
Exit Sub
End If

d = Target.Value
If d < 10100 Or d > 311299 Then ' Not a date, we do not transform the
content
Exit Sub
End If

s = Format(d, "000000")
Application.EnableEvents = False
Target.Value = DateSerial(Val(Mid(s, 5, 2)), Val(Mid(s, 3, 2)), Val(Mid(s,
1, 2)))
Application.EnableEvents = True

End Sub

Stephane Quenson
 
Thank you very much for your help. I tried the answer to my first question
and it worked just like I wanted. Then I tried the second answer but it had
some type of error. So thank you.
 
Thank you for your help. I tried the link you gave me and it worked for enter
my dates into C column. But doing so changed my B column because the formula
uses the dates in column C. Now column B shows numbers but not dates. I read
somewhere that this is the serial number format of the date. So I wanted to
know if there was a way to correct this. Any assistance is appreciated.
 
Unfortunately, some lines of code have been wrapped by the forum editor, so
when you see a line that does not start indented, simply remove the line
break between this line and the previous one.
 
Back
Top