Posts by shades

Re: Msgbox "ok"

Note though, that Kenneth's first response is helpful because it spells out the options.

Re: Msgbox "ok"

Redundant

Re: VBA Conditional Format Based On Multiple Conditions

Okay, this will be automatic (I only did 0 through 2).

Go to the worksheet where you want this. Then right-click on the sheet name and choose "View Code". This will bring up a window in VBE. Paste this into that window.

Private Sub Worksheet_Change(ByVal Target As Range)
' Conditional Formatting for more than 3 conditions
    Dim rng As Range
    ' Target is a range::therefore,it can be more than one cell
    ' For example,,someone could delete the contents of a range,
    ' or someone could enter an array..
    Set rng = Intersect(Target, Range("A1:A10"))
    If rng Is Nothing Then
        Exit Sub
    Else
        
        Select Case rng.Value
        Case 0
            rng.Interior.Color = RGB(255, 255, 255)
            rng.Font.Color = RGB(0, 0, 0)
        Case 1
            rng.Interior.Color = RGB(230, 230, 230)
            rng.Font.Color = RGB(0, 0, 0)
        Case 2
            rng.Interior.Color = RGB(204, 204, 204)
            rng.Font.Color = RGB(0, 0, 0)
        Case Else
            rng.Interior.Color = RGB(128, 128, 128)
            rng.Font.Color = RGB(0, 0, 0)
        End Select
    End If
End Sub

Go back to your worksheet and put a number (0, 1, 2) into any cell A1:A10.

Now, to add more conditions, just copy the Case 2 and the two following lines, and change your RGB numbers to match what you want. If you need white for the Font color, then use RGB (255,255,255)

Re: VBA Conditional Format Based On Multiple Conditions

Can you enclose a sample of your data, one worksheet with data and one with how you want the colors to look afterward?

Re: Use Of "or" In A Formula

Howdy.

=OR(C29=1,F29=1)

Re: VBA Conditional Format Based On Multiple Conditions

Take a look at this, which is a SELECT CASE approach, and uses RGB for Interior and Font:

Select Case Celli.Value
                    Case "AAA"
                        .Interior.Color = RGB(51, 102, 255)
                        .Font.Color = RGB(0, 0, 0)
                    Case "BBB":
                        .Interior.ColorIndex = 47
                        .Font.Color = RGB(255, 255, 255)
                    Case "CCC":
                        .Interior.ColorIndex = 32
                        .Font.Color = RGB(255, 255, 255)
                    Case "DDD"
                        .Interior.Color = RGB(51, 51, 153)
                        .Font.Color = RGB(255, 255, 255)
                    Case "EEEE"
                        .Interior.Color = RGB(255, 153, 0)
                        .Font.ColorIndex = RGB(0, 0, 0)
                     Case Else
                        .Interior.Color = RGB(128, 128, 128)
                        .Font.Color = RGB(255, 255, 255)
                 End Select

Re: VBA Conditional Format Based On Multiple Conditions

Instead of the Font, change it to Interior. And if you need to change each then add a coloumn on CFControl to accomodate both Interior and Font numbers.

As for the colors, you can experiment with the ColorIndex numbers (between 0 and 55) on the CFControl worksheet.

If that doesn't work, you can use RGB color combinations and change appropriately.

Re: Convertering All Numbers To Decimal

Howdy, and welcome to the board.

Have the person in Excel go to Tools > Options > Edit (tab), and see whether the box is checked for: Fixed Decimals. I suspect it is, and it is set for 3 decimals. Uncheck the box and it should work fine.

Re: Bar Chart With Marker

Perhaps this series of charts by Jon Peltier will work. It's worth looking at the setup of his data and the three types of charts used in combination.

Re: Bar Chart With Marker

I am taking a look, but also in the middle of a hot project.

One suggestion as you try also: don't use 3D charts at this stage. Sometimes they can obscure information, and with this kind of specialization of charts, 3D charts just get in the way of getting the chart correct.

Re: Bar Chart With Marker

Can you post what you have already done? Then we can work on that. No need for us to reinvent the wheel, so to speak. There are some possible solutions, but it works better to have the example to test it before posting a solution.

If it is sensitive data, just put in dummy data for illustration.

Re: Changing Mm/yy To Mm/dd/yy

Howdy, and welcome to the board.

Suppose your text is in B6, then in C6 put this:

=TEXT(LEFT(B6,2),"00")&"/01/"&TEXT(RIGHT(B6,2),"00")

Re: Bar Chart With Marker

Howdy. Can you post a sample of your data and what you are expecting?

Re: Vlookup Process

The attached has three worksheets.

Data: has the three columns you mentioned and two dynamic named ranges. "Type" is the one used and this is the reference formula:

=OFFSET(Base!$D$1,0,0,COUNTA(Base!$D:$D),2)

Control: has the alphabetical list of construction types, named range is "TypeList"

LUP: has the formula

Cell C4: Construction type to lookup (dropdown, which has validation based on TypeList named range).

Cell D4: Lookup formula:

=VLOOKUP(C4,Type,2)

In this case, it looks up the value in C4, then goes to the table "Type" and finds the corresponding value in the second column.

HTH

Re: Vlookup Process

Howdy. A couple of questions.

Will there be repeats in the type of construction? If so, what do you want to happen?

What are the types of construction (which will be used for the dropdown)?

What is the role of the the Columns number?

Will there ever be more than three columns?

Will you be adding to the list of 50+ items?

(Guess more than a couple of questions)

Re: Add Row In Sum Formula

Or put the SUM formula at the beginning of the list, that way it will add any additional rows (with some modification).

Re: Lookup 2 Columns And Match To 2 Other Columns

Howdy. Suppose your reference for the state is in D2, then with the INDEX approach it would be this:

=INDEX($H$3:$H$17,MATCH($A3&$B3,$E$3:$E$17,FALSE),MATCH($D$2,$H$2:$BE$2,FALSE))

Re: Function "replace" Missing For Mac Vb

Quote from ByTheCringe2

Ok, I'll move it back. Sorry.

Thanks for bringing this back. Appreciate it.

Re: Function "replace" Missing For Mac Vb

Essentially that is correct. There might still need to be tweaking, but it will get you in right ball park.

Re: Formatting Line Graph

If you need it continuous, then add the last value in the Actuals, into the column for Projected.