Text Issue

[GreenNinjaX]

Hello Folks,

This seems pretty simple and I am not sure if there is a solution to this. Here's my example:

A1: Dogs
A2: Cats
Do you like Cats?A3: Yes/No (Dropdown)
A4: IF(A3="Yes",A2,A1)

How I make it so "Cats" is in a larger bold text like A2 and if say "Dogs" is returned it will show that text. It only wants to return the text that is in A4. Is there a solution to this? If not is there away to combine a formula to run a macro to change font size when a certain event happens?

Thanks Everyone,

GNX

 

[hiker95]

GreenNinjaX,

You could use the Worksheet_Change Event for cell A3.

In my example cell A3's data validation is blank, Yes, or No.


Before any selection in cell A3:

Excel Workbook
	A	B
1	Dogs
2	Cats
3
4
5
Sheet1

If we pick Yes in cell A3 we get:

Excel Workbook
	A	B
1	Dogs
2	Cats
3	Yes
4	Cats
5
Sheet1

If we pick blank in cell A3 we get:

Excel Workbook
	A	B
1	Dogs
2	Cats
3
4
5
Sheet1

If we pick No in cell A3 we get:

Excel Workbook
	A	B
1	Dogs
2	Cats
3	No
4	Dogs
5
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Select the worksheet in which your code is to run
3. Right click on the sheet tab and choose View Code, to open the Visual Basic Editor
4. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
5. Press the keys ALT + Q to exit the Editor, and return to Excel

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
' hiker95, 03/16/2011
' http://www.mrexcel.com/forum/showthread.php?t=536631
If Intersect(Target, Range("A3")) Is Nothing Then Exit Sub
With Application
  .EnableEvents = False
  .ScreenUpdating = False
  Select Case UCase(Target.Value)
    Case "YES"
      With Range("A4")
        .Value = Range("A2").Value
        .Font.FontStyle = "Bold"
        .Font.Size = 12
      End With
    Case "NO"
      With Range("A4")
        .Value = Range("A1").Value
        .Font.FontStyle = "Regular"
        .Font.Size = 10
      End With
    Case ""
      With Range("A4")
        .Value = ""
        .Font.FontStyle = "Regular"
        .Font.Size = 10
      End With
  End Select
  .ScreenUpdating = True
  .EnableEvents = True
End With
End Sub

Then make a selection in cell A3.

 

[GreenNinjaX]

Thank you for your help. I am afraid my example might have been a bit to simplified so Code isn't working. I apologize let me give a better example.

Sheet2!A100=Sheet1!A3
Sheet1!A1= Cats (Times New Roman: 12 Bold)
Sheet1!A2= Dogs (Times New Roman: 11 Reg)
Sheet1!A3=IF(Sheet1!B1="Sheep"),Sheet1!A1,Sheet1!A2

So now I will have Cats in the right text format generated in Sheet1!A3
but not in Sheet2!A100 (and using the IF formula in Sheet2 does not generate the right text formatting either).

Thanks again for any help and sorry my response came a few days after you posted it.

 

[hiker95]

GreenNinjaX,

So we can get it right the first time.


Here are three possible ways to post small (copyable) screen shots directly in your post:

Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Excel Jeanie HTML 4 (contains graphic instructions).
http://www.excel-jeanie-html.de/html/hlp_schnell_en.php

or
RichardSchollar’s beta HTML Maker -...his signature block at the bottom of his post

or
Borders-Copy-Paste



If you are not able to give us screenshots:

To get the most precise answer, it is best to upload/attach a sample workbook (sensitive data scrubbed/removed) that contains an example of your raw data on one worksheet, and on another worksheet your desired results.

The structure and data types of the sample workbook must exactly duplicate the real workbook. Include a clear and explicit explanation of your requirements.

You can upload your workbook to www.box.net and provide us with a link to your workbook.

 

[GreenNinjaX]

Hi There,

Here is the link on box.net.

http://www.box.net/shared/4ui1ihnmvi

Sorry for the delay in response. I have duplicated the same aspects of my sheet in this example. Ultimate goal is to have Sheet1!A9 and Sheet2!A1 equal to the exact font style/type/size that are in Sheet1!A4 and
Sheet1!A5.

Thanks again for your help.

 

[hiker95]

GreenNinjaX,

Thanks for the workbook.


Sample raw data in worksheet Sheet1 with cell B7 data validation value "Yes":

Excel Workbook
	A	B
1
2	Dogs
3	Cats
4	Dogs
5	Cats
6
7	Do you like cats?	Yes
8
9	Cats
10
Sheet1

Then we change cell B7 data validation value "No":

Excel Workbook
	A	B
1
2	Dogs
3	Cats
4	Dogs
5	Cats
6
7	Do you like cats?	No
8
9	Dogs
10
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Select the worksheet in which your code is to run
3. Right click on the sheet tab and choose View Code, to open the Visual Basic Editor
4. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
5. Press the keys ALT + Q to exit the Editor, and return to Excel

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
' hiker95, 03/24/2011, Version 2
' http://www.mrexcel.com/forum/showthread.php?t=536631
If Intersect(Target, Range("B7")) Is Nothing Then Exit Sub
With Application
  .EnableEvents = False
  .ScreenUpdating = False
  Select Case UCase(Target.Value)
    Case "YES"
      With Range("A9")
        .Value = Range("A5").Value
        .Font.Name = "Times New Roman"
        .Font.FontStyle = "Bold"
        .Font.Size = 12
      End With
    Case "NO"
      With Range("A9")
        .Value = Range("A4").Value
        .Font.Name = "Times New Roman"
        .Font.FontStyle = "Regular"
        .Font.Size = 11
      End With
  End Select
  .ScreenUpdating = True
  .EnableEvents = True
End With
End Sub

Then make changes to the data validation cell B7.

 

[GreenNinjaX]

I am sick and haven't had a chance to try this yet but still appreciate such a quick response and will get back to you soon and let you know how I made out.